Ligth version of SUMPRODUCT()

[ExcelStefan]

Dear Excel-experts,

I have a workbook with several worksheets with data like this

Name\Date 1 2 3 4 5 6
Anthony 0 1 1 3 1 0
Ingrid 1 1 1 0 0 1
Anthony 1 0 1 1 0 0

I would like to get the sum of e.g. Anthony and date 3. Until now, I used SUMPRODUCT((Name="Anthony")*(Date=3); data_range) or SUMPRODUCT((Name="Anthony")*(Date=3)*(data_range)), which are the same. Now, I'm facing a calculation time problem (2 minutes to refresh the workbook).

So I thought, all I had to do was to work on Excel 2007, and to use SUMIFS() instead of SUMPRODUCT() (this would probably avoid multiplying 0 times 0 thousands of times). The problem that came up: SUMIFS() doesn't take an array as argument. Am I stuck with SUMPRODUCT(), or is there something like SUMIFS() which could take an array as a range, and horizontal and vertical vectors as sum_criterias?

Thanks!

[NBVC]

Try something like this:

=SUMIF(Name;"Anthony";INDEX(ArrayRange;0;MATCH(3;DATE)))

where ArrayRange is the summing matrix.

[Pete_UK]

Have you tried using:

=INDEX(data_range;MATCH("Anthony";Name;0);MATCH(3;Date;0))

where data_range, Name and Date are named ranges or the correct cell references for those ranges.

Much faster than SUMPRODUCT.

Hope this helps.

Pete

[NBVC]

There are multiple Anthony's Pete... I think he/she is trying to sum them up...

[Pete_UK]

There are multiple Anthony's Pete... I think he/she is trying to sum them up...

Ah, right - I didn't spot that.

Pete

[ExcelStefan]

Thanks guys!

Now, the problem is a little more complex - there are more conditions to meet.


First: I need to find out, how many people of the same job have how many instances at what period. Given:

Name Job
Anthony Carpenter
Ingrid Carpenter
Bob Driver

And the same table as above

Name\Period 1 2 3 4 5 6
Anthony 1 0 3 1 3 0
Ingrid 0 2 1 3 4 5
Anthony 0 5 6 1 2 4


Second problem:
There are seven days with the same week number. Therefore, they should add, except for weekends (first and last day of the same week).

Name\Period 1 1 1 2 2 2 2 2 2 2 3 3

In the SUMPRODUCT() solution, one could shift the week number row to the left and to the right to only count the second to the sixth day of the week.


Do you guys think, this is possible with SUMIF() or SUMIFS()?

Thanks!

[NBVC]

The formula I gave you assumed one matching column... if you are going to match variable rows and variable columns, then you need to go to sumproduct again...

[ExcelStefan]

Ok, thanks!

I just hoped, SUMIFS() was a light version of SUMPRODUCT(), which would stop summing once a condition is not met - instead of multiplying values many times times zero.