# Help with formula syntax

#### virtuosok

##### Board Regular
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
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
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)``

#### virtuosok

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

Replies
27
Views
2K
Replies
2
Views
119
Replies
2
Views
56
Replies
3
Views
58
Replies
2
Views
93

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.

### Which adblocker are you using?

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

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