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

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### etaf

##### Well-known Member
i don't follow completely
You are checking an Array with an IF ( Isblank()

then doing 2 different sums if TRUE or FALSE

=IF(ISBLANK(\$A\$2:\$A\$6),
TRUE
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),
FALSE
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))

So IF A2 is Blank, then use the first formula
But I suspect you also want to test to see if
B2 & E2 is blank

IS this and AND needed

=AND( ISBLANK(A2),ISBLANK(B2),ISBLANK(E2) ) type of formula
OR a counta() count cells that are not empty

Sorry i'm not following the requirement
Can you give a little more detail
maybe a matrix
Depending on what cells A,B,E are blank

#### virtuosok

##### Board Regular
Thanks etaf,
In terms of checks for blanks, I only need to check for A2:A6; for subsequent calc it doesn't matter much if other cells are blank or not. The formula as it stands now works for my needs - I just need to expand the selection for B2, E2, and A2 into B2:B6, E2:E6, and A2:A6, accordingly. To put it differently, I used to have only row 2 for inputs, now I added rows 3-6 to cover more scenarios at the same time... need to tweak the above formula accordingly.

Hope this makes sense.
OR, let me make it less complicated. For this piece
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)
How can I calculate total SUMIFS not only for A2 (as it currently stands), but also for A3, A4, A5, and A6 so that they all are verified independently and then added up?

#### jasonb75

##### Well-known Member
That is nothing like the requirement vaguely implied by your original post.

See if this works, it is what you have now asked for, but I have my doubts that it is what you need.
Excel Formula:
``=SUM(SUMIFS('Labor report'!\$N\$2:\$N\$200000, 'Labor report'!\$AB\$2:\$AB\$200000,"*" & \$B\$2:\$B\$6 & "*",'Labor report'!\$L\$2:\$L\$200000,"*" & \$E\$2:\$E\$6 & "*",'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))``

#### virtuosok

##### Board Regular

Apologies for my complicated explanation, give me another chance...
This is what I had initially and it worked perfectly for my purposes:

The formula was as follows:
=IF(ISBLANK(\$A\$2),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\$14,'Labor report'!\$D\$2:\$D\$200000,\$B15),SUMIFS('Labor report'!\$N\$2:\$N\$200000, 'Labor report'!\$K\$2:\$K\$200000,"*" & \$A\$2 & "*",'Labor report'!\$B\$2:\$B\$200000,E\$14,'Labor report'!\$D\$2:\$D\$200000,\$B15))
In other words, if there was a name/surname in cell A2, the formula would do the calc with it; otherwise it would do the calc based on entries in cells B2 and E2 (if anything there).
Now I would need to do analysis based on several inputs and not just one; these will be either in cells A2:A6, or in cells B2:B6 and E2:E6. There might be all 5 cells in the range with a condition, or only some of them.
Scenario 1:

Do I do SUM(SUMIFS A2, SUMIFS A3, SUMIFS A4, SUMIFS A5, SUMIFS A6), or SUM(SUMIFS A2:A6), or SUMIFS A2 + SUMIFS A3... or is there another rule I need to try? Same for
Scenario 2:

#### Attachments

• 1619271892504.png
18 KB · Views: 1

#### jasonb75

##### Well-known Member
Trying to simplify this a bit, are you looking for all possible combinations? For example, if the criteria were entered as in rows 2 and 3 below, should the formula be looking at all of the combinations listed from row 6?
22.04.21.xlsx
ABC
1NameCodeTitle
2SmithAUSManager
3JonesUSACleaner
4
5All combinations
6SmithAUSManager
7SmithUSAManager
8JonesAUSManager
9JonesUSAManager
10SmithAUSCleaner
11SmithUSACleaner
12JonesAUSCleaner
13JonesUSACleaner
Sheet3

#### virtuosok

##### Board Regular

No, there are actually less combinations that I'm after.
In the above example, columns A, B, and C won't have entries at the same time. It will be either A, or alternatively, B&C. But, some of the cells in a given column might be blank, so I don't want this condition to be counted twice. In other words, the two "scenarios" with screenshots that I posted above are mutually exclusive.
I'm also posting blinded workbook that I'm working with
Blinded report v4.0.xlsm
The formulas which I'm trying to develop belong to cells E15:M67 on tab Calc

#### jasonb75

##### Well-known Member
I haven't tested this formula, there are still a couple of grey areas in the requirement.

As you're using excel 2016, this must be array confirmed with Ctrl Shift Enter.
Excel Formula:
``=SUM(IF(ISBLANK(\$A\$2:\$A\$6),SUMIFS('Labor report'!\$N\$2:\$N\$200000, 'Labor report'!\$AB\$2:\$AB\$200000,"*" & \$B\$2:\$B\$6 & "*",'Labor report'!\$L\$2:\$L\$200000,"*" & \$E\$2:\$E\$6 & "*",'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:\$A\$6 & "*",'Labor report'!\$B\$2:\$B\$200000,E\$15,'Labor report'!\$D\$2:\$D\$200000,\$B16)))``

edit:- a second option, that would work slightly differently to the first. Still needs to be array confirmed.
Excel Formula:
``=SUM(IF(ISBLANK(\$A\$2:\$A\$6),SUMIFS('Labor report'!\$N\$2:\$N\$200000, 'Labor report'!\$AB\$2:\$AB\$200000,"*" & \$B\$2:\$B\$6 & "*",'Labor report'!\$L\$2:\$L\$200000,"*" & TRANSPOSE(\$E\$2:\$E\$6) & "*",'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:\$A\$6 & "*",'Labor report'!\$B\$2:\$B\$200000,E\$15,'Labor report'!\$D\$2:\$D\$200000,\$B16)))``

Last edited:

#### virtuosok

##### Board Regular
Thank you jasonb75! This is very close to what I need. Two comments only:
1. For range \$A\$2:\$A\$6, the formula works impeccably for my needs only when all cells, from A2 to A6, have some inputs. Otherwise if one (or more) of these cells are empty, outputs are incorrect as I am getting a much higher total than expected.
2. For ranges in columns B and E, it's the same problem, but since these are 2 columns it's a bit more complicated to explain. Let's see if we can fix A column and then I may be able to replicate the solution for B and E...

#### jasonb75

##### Well-known Member
Are the wildcards needed on all 3 ranges?
I suspect that they are the biggest part of the problem.

Replies
27
Views
2K
Replies
2
Views
119
Replies
2
Views
56
Replies
3
Views
58
Replies
29
Views
139

1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

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

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