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)
 

virtuosok

Board Regular
Joined
Sep 2, 2020
Messages
128
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,608
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 2, 2020
Messages
128
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,608
Office Version
  1. 365
Platform
  1. Windows
Did you array confirm it? I forgot to mention that :oops:
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,608
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

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
Joined
Sep 2, 2020
Messages
128
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,608
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Sep 2, 2020
Messages
128
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,608
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 2, 2020
Messages
128
Office Version
  1. 2016
Platform
  1. Windows
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)))
 

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