SumProduct for multiple criteria across multiple tabs

zizzabell

New Member
Joined
Feb 2, 2011
Messages
15
I have a spreadsheet that has many tabs, each for a day's worth of work. I am trying to work on a formula that would sum together all days but only at specific farms and in specific blocks within the farms. The dates are 10-22 thru 12-20 (corresponding with the tab names) and the Farm name is in cells B6 thru Q6 on each tab (there could be work at multiple farms each day) and the block #'s are in cells B7 thru Q7 (again there could be work at multiple blocks, either in different or the same farms). The quantities that I want to sum are in cells B9 thru Q20 on each tab. The farm I want it to reference is in A1 and the block I want it to reference is in B19.

I have in the past used the following formula within a single tab and it worked great. I thought it would work across the different tabs like you would with a simple sum function, but apparently not. Can anyone help me tweak it? I am currently getting a result of #VALUE!.

{=SUMPRODUCT(--('10-22'!B6:'12-20'!Q6=A1),--('10-22'!B7:'12-20'!Q7=B19),('10-22'!B9:'12-20'!Q20))}

On another tab, I summed the amounts (using a single criteria of which farm) using Sumif and referencing each tab individually and adding them all together. It's a long, messy formula, but it got the job done. Now I need the same done including the farm AND the block numbers and if I can avoid adding ~60 sumproduct formulas together, that would be great!
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,299
Probably best to have a formula for each worksheet, and then sum the results from those sheets. Otherwise, list the sheet names in a vertical range of cells, let's say T2:T60, then try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IFERROR(SUMIF(OFFSET(INDIRECT("'"&$T$2:$T$60&"'!B9:Q20"),,IF(T(OFFSET(INDIRECT("'"&$T$2:$T$60&"'!B6:Q6"),,COLUMN(INDIRECT("B6:Q6"))-COLUMN(INDIRECT("B6")),,1))=A1,IF(T(OFFSET(INDIRECT("'"&$T$2:$T$60&"'!B7:Q7"),,COLUMN(INDIRECT("B7:Q7"))-COLUMN(INDIRECT("B7")),,1))=B19,COLUMN(INDIRECT("B9:Q20"))-COLUMN(INDIRECT("B9")),""),""),,1),"<>"),0))

Hope this helps!
 
Last edited:

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,299
Actually, I just realized we're dealing with "block numbers", which I'm assuming are numerical values not text values. If so, the second "T" function needs to be changed to "N"...

=SUM(IFERROR(SUMIF(OFFSET(INDIRECT("'"&$T$2:$T$60&"'!B9:Q20"),,IF(T(OFFSET(INDIRECT("'"&$T$2:$T$60&"'!B6:Q6"),,COLUMN(INDIRECT("B6:Q6"))-COLUMN(INDIRECT("B6")),,1))=A1,IF(N(OFFSET(INDIRECT("'"&$T$2:$T$60&"'!B7:Q7"),,COLUMN(INDIRECT("B7:Q7"))-COLUMN(INDIRECT("B7")),,1))=B19,COLUMN(INDIRECT("B9:Q20"))-COLUMN(INDIRECT("B9")),""),""),,1),"<>"),0))

Hope this helps!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,814
Messages
5,525,047
Members
409,616
Latest member
ULFireTester

This Week's Hot Topics

Top