时间戳

首页 > 解决方案 > How can I use 3D SUMIF function over a single worksheet?

excel - How can I use 3D SUMIF function over a single worksheet?

问题描述

I have a XL Doc with 2 worksheets. In one worksheet (Scope) I have all the resources listed down with their allocation and capacity. In the second worksheet (Summary)I will be including tickets to the resources that I mentioned in my first worksheet and the hours for each ticket will also be inserted.

enter image description here

1st Worksheet Name - Summary

2nd Worksheet Name - Scope

There are 3 columns in Scope sheet namely, Resource , Ticket and Hours. In Summary Sheet I have Resource and Allocation columns.

I need to get the total hours for a resource in Summary sheet and display it on Allocation column. Note: A resource will have multiple tickets allocated.

I tried below formula in Summary sheet. But it shows value 0, even I inserted hours in Scope sheet.

=SUMPRODUCT(SUMIF(INDIRECT("'"&Scope&"'!"&"A2:A500"),C4,INDIRECT("'"&Scope&"'!"&"I2:P500")))

C4 cell contains the name of the resource.

Please help me to sort this.

enter image description here

标签： excelexcel-formulavba

解决方案

Try this,

=SUMPRODUCT(SUMIF(Scope!A2:A500, C4, INDIRECT(ADDRESS(2, COLUMN(I:P), 4, 1, "Scope")&":"&ADDRESS(500, COLUMN(I:P), 4, 1))))

推荐阅读