Help with formula syntax

[virtuosok]

Hi,
I have the following formula:

=IF(ISBLANK($A$2:$A$6),SUMIFS('Labor report'!$N$2:$N$200000, 'Labor report'!$AB$2:$AB$200000,"*" & $B$2 & "*",'Labor report'!$L$2:$L$200000,"*" & $E$2 & "*",'Labor report'!$B$2:$B$200000,E$15,'Labor report'!$D$2:$D$200000,$B16),SUMIFS('Labor report'!$N$2:$N$200000, 'Labor report'!$K$2:$K$200000,"*" & $A$2 & "*",'Labor report'!$B$2:$B$200000,E$15,'Labor report'!$D$2:$D$200000,$B16))

For all 3 bits "*" & $x$y & "*" within the formula, how can I tweak it to:
- not only look into $B$2, but $B$2:$B$6 (for each of the cells independently; some may be blank but others will have an entry)
- not only look into $E$2, but $E$2:$E$6 (for each of the cells independently; some may be blank but others will have an entry)
- not only look into $A$2, but $A$2:$A$6 (for each of the cells independently; some may be blank but others will have an entry)

 

[jasonb75]

You can't use <> with multiple critera, while it would work if the syntax was correct ("<>"&$B16:$B68), it would give wildly incorrect results. The result would be <>B16 + <>B17 +<>B18, anything that was equal to B17 would be double counted (once because it is <> B16, and a second time because it is <>B18). Anything that was not equal to any of the 3 cells would be counted a 3 times in the results.

In addition to this, you can only a maximum of 2 arrays as criteria in sumifs (or other similar formulas) when you want all criteria applied to all rows. With more than 2 arrays, those of the same orientation will be evaluated on an 'AND' basis. In addition, arrays with the same orientation must be equal in size. Multiple criteria in the same column are of vertical orientation, criteria in a row are horizontal. In the formula, the transpose function converts one from vertical to horizontal.

I'm not even sure that it will be possible with sumproduct, but I'll give it some thought and see if I can find something that looks like it might work.

 

[jasonb75]

I'm not even sure that it will be possible with sumproduct, but I'll give it some thought and see if I can find something that looks like it might work.

Thinking out of the box a little, is this formula just for the last row of the table, effectively to summarise 'everything else' that meets the criteria in A2:A6, B2:B6, E2:E6 and E15?

If that is the case, then the easiest way would be to remove the ,'Labor report'!$D$2:$D$50000,$B69 parts from both SUMIFS sections of the formula, then subtract the total of the previous formulas from the result.

=IF(COUNTA($A$2:$A$6)=0,SUM(SUMIFS('Labor report'!$N$2:$N$50000, 'Labor report'!$AB$2:$AB$50000,IFERROR($B$2:INDEX($B$2:$B$6,MATCH("zzz",$B$2:$B$6)),"")&"*",'Labor report'!$L$2:$L$50000,"*"&IFERROR(TRANSPOSE($E$2:INDEX($E$2:$E$6,MATCH("zzz",$E$2:$E$6))),"")&"*",'Labor report'!$B$2:$B$50000,E$15)),SUM(SUMIFS('Labor report'!$N$2:$N$50000, 'Labor report'!$K$2:$K$50000,"*"&IFERROR($A$2:INDEX($A$2:$A$6,MATCH("zzz",$A$2:$A$6)),"")&"*",'Labor report'!$B$2:$B$50000,E$15)))-SUM(E$16:E$68)

 

[virtuosok]

Awesome! Yes this does the trick, thanks for another creative solution!!!