# 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)

#### virtuosok

##### Board Regular
I'm afraid yes... I'm not as concerned about B and E range, as long as there's a solution for A range. When I do the calc separately for individual inputs in A range with my legacy formula and then summarize everything manually, I am getting the same result as your formula returns when all cells in column A are completed. However, if 1 or more of them is empty, this is where the problem kicks in.

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### jasonb75

##### Well-known Member
See if this gives the correct result for the A range only. I'm not even convinced that it will work at all, but I can't download the attached file on this computer for testing.
Excel Formula:
``=SUM(SUMIFS('Labor report'!\$N\$2:\$N\$200000, 'Labor report'!\$K\$2:\$K\$200000,{"","","?*","?*"} & \$A\$2:\$A\$6 & {"","?*","?*",""},'Labor report'!\$B\$2:\$B\$200000,E\$15,'Labor report'!\$D\$2:\$D\$200000,\$B16))``

For the B and E range (using your earlier picture) should the 'manager' criteria be applied to AUS as well as USA, or should the AUS row return all job titles as the corresponding cell is empty?

#### virtuosok

##### Board Regular
Thanks for trying relentlessly jasonb75!
This formula though returns zeros, regardless of inputs in column A (blanks, all cells completed or partially completed)
Which is a pity, as it looks so elegant

Regarding B and E range, the "manager" criterion would apply to both countries (or, if there are more than 2 countries in column B, then to all of them noted there). Thinking about this further, I don't think I will ever have more than 1 entry in column E. I can just live with E2, if there's a solution to everything else.

Last edited:

#### jasonb75

##### Well-known Member
Did you array confirm it? I forgot to mention that

#### jasonb75

##### Well-known Member

Note that the formula is based on the file attached to your earlier post, which uses slightly different ranges to the formulas that you have posted.

This formula goes into E15 and must be array confirmed before using the fill handle to populated the table.

Excel Formula:
``=SUM(IF(ISBLANK(\$A\$2:\$A\$5),SUMIFS('Labor report'!\$N\$2:\$N\$200000, 'Labor report'!\$AB\$2:\$AB\$200000,IF(\$B\$2:\$B\$5<>"","*"&\$B\$2:\$B\$5&"*",""),'Labor report'!\$L\$2:\$L\$200000,IF(\$B\$2:\$B\$5<>"","*"&\$E\$2:\$E\$5&"*",""),'Labor report'!\$B\$2:\$B\$200000,E\$14,'Labor report'!\$D\$2:\$D\$200000,\$B15),SUMIFS('Labor report'!\$N\$2:\$N\$200000, 'Labor report'!\$K\$2:\$K\$200000,IF(\$A\$2:\$A\$5<>"","*"&\$A\$2:\$A\$5&"*",""),'Labor report'!\$B\$2:\$B\$200000,E\$14,'Labor report'!\$D\$2:\$D\$200000,\$B15)))``

A name in A2 and a country code in B3 will be calculated independently and added together, meaning that rows where both criteria are met will be duplicated in the result.

Job title can only be used as a secondary criteria with country code, it does not appear possible to use an and/or criteria with empty cells without erroneous results. I'll take another look later, there may be something I haven't thought of yet.

#### virtuosok

##### Board Regular
THANK YOU jasonb75 for your patience, creativity, tenacity and goodwill
This works!
My only remaining question is about the results when using the optional job title function... I'm not sure this component works.
E.g. let's take Jul2020, and calculate output for code 03.11.02 in column B, and let's input USA and SRB as countries in B2:B3.
Now if I enter specialist in E2, I obtain 43.5 hours via formula.
43.5 hrs is the number of hours for all job titles, which include US.Sr Site Contracts Specialist (33 hrs that month), US.Sr Site Contracts Specialist (1 hr), and RS.SSU Regulatory Coordinator (9.50 hrs)... I thought I should be getting 34 hrs as a result?
Or, when I tried to enter another title in E2 instead, e.g. lead - this one should return 0 for Jul2020, but I get 10.5 hrs... can't find why it works this way.

#### jasonb75

##### Well-known Member

E.g. let's take Jul2020, and calculate output for code 03.11.02 in column B, and let's input USA and SRB as countries in B2:B3.
Now if I enter specialist in E2, I obtain 43.5 hours via formula.
Going on this scenario, the formula would be returning USA Specialist (33.0) and all SRB (10.5) because E3 is blank. Based on your earlier responses, I was under the impression that it would be B2 and E2, B3 and E3, etc.

The main issue here is the presence of blank cells in the criteria, so taking another approach in order to eliminate them, this appears to be doing what you are asking for.
Again, must be array confirmed with Ctrl Shift Enter in the first cell before using the drag handle to fill the table.
Excel Formula:
``=IF(COUNTA(\$A\$2:\$A\$5)=0,SUM(SUMIFS('Labor report'!\$N\$2:\$N\$50000, 'Labor report'!\$AB\$2:\$AB\$50000,"*"&IFERROR(\$B\$2:INDEX(\$B\$2:\$B\$5,MATCH("zzz",\$B\$2:\$B\$5)),"")&"*",'Labor report'!\$L\$2:\$L\$50000,"*"&IFERROR(TRANSPOSE(\$E\$2:INDEX(\$E\$2:\$E\$5,MATCH("zzz",\$E\$2:\$E\$5))),"")&"*",'Labor report'!\$B\$2:\$B\$50000,E\$14,'Labor report'!\$D\$2:\$D\$50000,\$B15)),SUM(SUMIFS('Labor report'!\$N\$2:\$N\$50000, 'Labor report'!\$K\$2:\$K\$50000,"*"&IFERROR(\$A\$2:INDEX(\$A\$2:\$A\$5,MATCH("zzz",\$A\$2:\$A\$5)),"")&"*",'Labor report'!\$B\$2:\$B\$50000,E\$14,'Labor report'!\$D\$2:\$D\$50000,\$B15)))``

Personally, I would use a pivot table for this, but many people dislike them when it comes to filtering multiple criteria.

#### virtuosok

##### Board Regular
Thank you jasonb75 indeed!
I have pressure tested various scenarios, and it works like a charm!
There's one minor issue I noticed though for country code, can this be fixed somehow?
If I input AUS in B2, the formulas look for Australia as they should, but also add results for Germany due to partial match: DEU-Neu-Isenbrg-TriforumHausC1
Same might happen with other combinations. I deleted the bit in red below as country code is always at the beginning in column AB on source tab. I think it works - can you possibly confirm if I haven't butchered the formula?
=IF(COUNTA(\$A\$2:\$A\$5)=0,SUM(SUMIFS('Labor report'!\$N\$2:\$N\$50000, 'Labor report'!\$AB\$2:\$AB\$50000,"*"&IFERROR(\$B\$2:INDEX(\$B\$2:\$B\$5,MATCH("zzz",\$B\$2:\$B\$5)),"")&"*",'Labor report'!\$L\$2:\$L\$50000,"*"&IFERROR(TRANSPOSE(\$E\$2:INDEX(\$E\$2:\$E\$5,MATCH("zzz",\$E\$2:\$E\$5))),"")&"*",'Labor report'!\$B\$2:\$B\$50000,E\$14,'Labor report'!\$D\$2:\$D\$50000,\$B15)),SUM(SUMIFS('Labor report'!\$N\$2:\$N\$50000, 'Labor report'!\$K\$2:\$K\$50000,"*"&IFERROR(\$A\$2:INDEX(\$A\$2:\$A\$5,MATCH("zzz",\$A\$2:\$A\$5)),"")&"*",'Labor report'!\$B\$2:\$B\$50000,E\$14,'Labor report'!\$D\$2:\$D\$50000,\$B15)))

#### jasonb75

##### Well-known Member
That is quite a common problem with wildcard matching, I had been focusing on the other problems occurring and not even given this consideration.
country code is always at the beginning in column AB
If this changes, then it could be problematic, however, with the existing format your edit to the formula should work as required.

#### virtuosok

##### Board Regular
This works like a charm!
One other question. How can I tweak the below formula so that the very last bit - \$B69 - is not looking into cell B69, but looks into "anything that is not part of B16:B68" instead? I tried to replace \$B69 with "<>\$B16:\$B68"
This didn't work out. Should I try something like =SUMPRODUCT(--ISNA(MATCH( instead?
Cells B16:B68 are formatted as General and have entries like 02.01.00

=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,'Labor report'!\$D\$2:\$D\$50000,\$B69)),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,'Labor report'!\$D\$2:\$D\$50000,\$B69)))

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