Help with formula syntax

virtuosok

Board Regular
Joined
Sep 2, 2020
Messages
128
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I have the following formula:
Excel 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

Well-known Member
Joined
Dec 30, 2008
Messages
12,608
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,608
Office Version
  1. 365
Platform
  1. Windows
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.

Rich (BB code):
=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)
 
Solution

virtuosok

Board Regular
Joined
Sep 2, 2020
Messages
128
Office Version
  1. 2016
Platform
  1. Windows
Awesome! Yes this does the trick, thanks for another creative solution!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,472
Messages
5,636,516
Members
416,920
Latest member
Riskyplan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top