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

 

[Dave Patton]

What version of Excel are you using?

Can you install and use an add-in such as Morefunc?

Did you consider putting the data on one sheet instead of multiple sheets?

You can try one of the following:

I concatenated the 2 criteria (see "L8") and I added a helper column "L" on each sheet that concatenated the criteria columns.


=SUMPRODUCT(SUMIF(INDIRECT("'"&MnthTabs&"'!L9:L55"),L8,INDIRECT("'"&MnthTabs&"'!H9:H55")))

or with Morefunc

=SUMPRODUCT(--(THREED(Start:End!B9:B55)=K8),--(THREED(Start:End!C9:C55)=D16),(THREED(Start:End!H9:H55)))

 

[dwgnome]

Sorry I forgot to put that at the end of my original post. I am using Excel 2003 and although I am aware of Morefunc, I cannot use that for the workbook will be at my work (also Excel 2003) as the computer/software is strictly controlled. I cannot put the data on one sheet so I am left with what I originally requested. I prefer no helper columns as I will be copying and draging the formula accross multiple columns and rows. The first formula you provided only provides for one criteria. What I need is the THREED version in non Morefunc formulation. Is that doable?

 

[Dave Patton]

"The following works but looking for a way to simplify: "

An approach to simplify the solution will certainly be a good idea.
With the constraints that you cite, a solution would be fairly complex.

Could you put your sumproduct formulas on each sheet and then summarize on the summary sheet?



"The first formula you provided only provides for one criteria. "

N.B. I stated that I concatenated the 2 criteria into "L8" =K8&"|"&D16.

However, you stated that you do not want a helper column so that approach doesn't work.

 

[dwgnome]

I don't think the sum product on each sheet will work as the parameters k8 and d16 are on the summary sheet and they change as I drag the formula down and to the right.

Can the THREED formula be transformed to Excel native functions? As that solution should work.

 

[T. Valko]

I don't think the sum product on each sheet will work as the parameters k8 and d16 are on the summary sheet and they change as I drag the formula down and to the right.

Can the THREED formula be transformed to Excel native functions? As that solution should work.

Here's an example.

http://tinyurl.com/d8apqk

 

[dwgnome]

Biff,

Thank you for the reference. Unfortunately I cannot decipher which formula to use. Could you adapt one of those formulas to my situation, as I don't understand the referenced ones.

 

[T. Valko]

Biff,

Thank you for the reference. Unfortunately I cannot decipher which formula to use. Could you adapt one of those formulas to my situation, as I don't understand the referenced ones.

I'll take a look at it tomorrow. I'm getting ready to call it a day.

 

[T. Valko]

I'll take a look at it tomorrow. I'm getting ready to call it a day.

Try this...

The sheet names, MnthTabs, must be a horizontal range of cells.

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

 

[dwgnome]

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.