Sum Across Worksheets with Two Criteria

[dwgnome]

I am in need of a formula that can sum ranges across worksheets with more than one criteria.

The following works but looking for a way to simplify:

=SUMPRODUCT(--('Jan 2012'!$B$9:$B$55=K$8),--('Jan 2012'!$C$9:$C$55=D$16),'Jan 2012'!$H$9:$H$55)+SUMPRODUCT(--('Feb 2012'!$B$9:$B$55=K$8),--('Feb 2012'!$C$9:$C$55=D$16),'Feb 2012'!$H$9:$H$55)

I created a list of sheet names (Jan 2012, Feb 2012) and called it MnthTabs. I need to do this for all months of the year, so the formula above would get too unweildy.

I tried the following but it does not work.

=SUMPRODUCT(SUMIF(INDIRECT("'"&MnthTabs&"'!$B$9:$B$55"),K$8,INDIRECT("'"&MnthTabs&"'!$H$9:$H$55"))+SUMIF(INDIRECT("'"&MnthTabs&"'!$c$9:$c$55"),D$16,INDIRECT("'"&MnthTabs&"'!$H$9:$H$55")))

This results in a value, but the wrong value. It sums all values in H9:H55 for each condition found when K8="PI" and D16="Edgar Allen" independently. I need the sum only when both conditions exist in the ranges B9:B55 and C9:C55.

Thanks

 

[T. Valko]

Biff,

Most excellent! It apears to work as needed. I will run a more thorough test when I get back to my computer at home as I am traveling and just borrowed a friends ipad.

Could you explain why the need for the Offset function as all of the ranges are already defined and I thought arrays automatically start at the top of the array. Also what would the formula look like if I changed the list of worksheets (months) to vertical instead of horizontal.

Again thank you for stepping in with your expertise.

The OFFSET is "stepping thru" the sheet range one cell at a time.

In this application the sheet names must be in a horizontal array. If the sheet names are in a vertical array then you'd have to transpose them like this:

Now the formula must be array entered**.

=SUMPRODUCT(--(T(OFFSET(INDIRECT("'"&TRANSPOSE(MnthTabs)&"'!B9:B55"),ROW(INDIRECT("B9:B55"))-9,,))=K8),--(T(OFFSET(INDIRECT("'"&TRANSPOSE(MnthTabs)&"'!C9:C55"),ROW(INDIRECT("C9:C55"))-9,,))=D16),N(OFFSET(INDIRECT("'"&TRANSPOSE(MnthTabs)&"'!H9:H55"),ROW(INDIRECT("H9:H55"))-9,,)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[dwgnome]

Thank you for the explanation and the solution.

 

[T. Valko]

Thank you for the explanation and the solution.

You're welcome. Thanks for the feedback!

 

[wardy0601]

Biff, you're a champion. That is a wonderful solution!!

 

[T. Valko]

Biff, you're a champion. That is a wonderful solution!!

Thanks!

If you're using Excel 2007 or later it's a bit easier thanks to the COUNTIFS and SUMIFS functions.