SumProduct for multiple criteria across multiple tabs

[zizzabell]

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!

 

[Domenic]

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!

 

[Domenic]

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!