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)

 

[virtuosok]

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.

 

[jasonb75]

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.

=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]

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.

 

[jasonb75]

Did you array confirm it? I forgot to mention that

 

[jasonb75]

I managed to download your file, this appears to work correctly.

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.

=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]

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]

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.

=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]

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]

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]

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