Formula Help - Using IF & SUMIF

[oliinla]

Task
I have a commission report for my team - I've managed to get the formule correct to total each person by booking agent
BUT, I need to have a check/balance......IF THE RANGE DOES NOT INCLUDE THEIR BKAGENT INFO - SUM IT

So, for each employee, its BZ70XX - where XX is their individual identifier
Kelley Is KT (BZ70KT)
=SUMIF(D:D,"*BZ70KT*",M:M)

And that works for BZ70, and in one cell per staff member (BZ70YS BZ70YT BZ70OY and BZ70YN)

So I tried to make the exception......if the range doesn't have one of the above, AND E3="2".....sum it - what am i doing wrong:
=IF(E3="2",SUMIF(D:D,"<>*OY*,*KT*,*YN*,*YS*,*YT*",M:M))

I've tried it with *BZ70OY*,*BZ70KT*,*BZ70YN*,*BZ70YT* aswell - but it's a no go

(and the reason I need it to only sum the additional, when "2" is present...is because the spreadsheet has subtotals.... so it's summing those....which is not helpful....to exclude the subtotal rows, I'm saying it must have a Branch # 2 in Column E...which they do not

HELP!

 

[Oaktree]

Welcome to the board.

The answer to your inquiry is that "<>*OY*,*KT*,*YN*,*YS*,*YT*" isn't a criterion for not equal to (5 different text strings). Instead, what's happening is that Excel is treating that as not equal to ONE crazy text string with a bunch of wild cards. So, it's very likely going to sum your whole range as though no IF criterion was there at all...

Instead, try using SUMIFS and working through your criteria one at a time such as:

=IF(E3="2",SUMIFS(M:M,D:D,"<>*OY*",D:D,"<>*KT*",D:D,"<>*YN*",D:D,"<>*YS*",D:D,"<>*YT*"),"E3 <> 2")

Note that ="2" is specifically looking for the text value "2" and not the number 2. If you want the number 2, you should remove the quotes.

 

[oliinla]

Thanks Oaktree! Hmmm, another issue

Okay, so "2" vs 2 makes sense - weirdly it's auto-formatted a GENERAL - so I'd assume that treats it like Text wth the green corner marker?

Basically speaking (i hope)
There's ONE value that is not part of my Team in Column D......

So here's the 4 formula working to SUM each colleague.

=SUMIF(D:D,"*BZ70KT*",M:M)
=SUMIF(D:D,"*BZ70YS*",M:M)
=SUMIF(D:D,"*BZ70YT*",M:M)
=SUMIF(D:D,"*BZ70YN*",M:M)
=SUMIF(D:D,"*BZ70OY*",M:M)

So I want a formula to Sum anything that isn't part of my team ID's......but exclude the subtotal rows (which happen to NOT have a "2" in E....which differentiates them)

 

[Oaktree]

The formatting of the cell doesn't change the underlying value. For example, if you type the number 2 in cell A1 and enter this in B1, you'll get TRUE:

=(A1=2)

But, if you select A1 again and type a single quote (', which is a text qualifier for Excel) before the number 2, your formula in B1 will change to FALSE. That's because Excel treats the text value "2" and the number 2 differently. Even if you changed the '2 text qualified entry to be *formatted* as a number, it will still be treated as a text value for calculations.

As a quick aside, this becomes useful with large numbers, where Excel can only handle 15 digits of precision. If you try typing a 16 digit credit card in any cell, you'll find that Excel makes the last digit 0 regardless of what you type in. BUT, if you put the single quote text qualifier in first, you can type the full credit card number without issue.

If you are already capturing the sum of your team's 5 values elsewhere, maybe it's easier to just calculate the overall sum and then subtract your team's totals that you already have? That's probably a better idea than the 5 criteria SUMIFS suggestion I proposed in my previous post...

 

[oliinla]

Thanks - I totally agree, and yes the total is being captured on 2 sheets, but it's never the same cell or even the same row.....any ideas? Each month has a different number of transactions....so the number of rows on the commission sheet will change for instance.....or is the only option to then manually point to the relevant cell? We could do Sum of Column M, but still need to exclude the subtotals that the report includes.....so using the E=2 rule i guess?

 

[Oaktree]

When you say there are subtotals... do you mean they've been created via data --> subtotal (using Excel's outlining feature)?

Or just that the data has rows for subtotals that were either calculated with formulas or hardcoded as an extract from a different program?

If they've been created with Excel's outlining feature, you can use =SUBTOTAL(9,range) instead of =SUM(range) and the subtotals will be ignored/not double counted.

If that's not the case, does the range have a "Grand Total" at the bottom that you could pull from with something like =INDEX(B:B,MATCH("Grand Total",A:A,0)) or similar?