Link to home
Start Free TrialLog in
Avatar of Jenedge73
Jenedge73Flag for Afghanistan

asked on

Sumifs criteria not equal to blank

I am trying to make a sumifs stmt and the very last criteria should bee not equal to "BlanK"  how do you write that?  below you will see the formula.
SUMIFS($AB$14:$AB$51263,$D$14:$D$51263,"Revenue",$E$14:$E$51263,$E14,$K$14:$K$51263,$K14,$AC$14:$AC$51263,$AC14,$AP$14:$AP$51263,"<>SL",$AP$14:$AP$51263,"<>""")
Avatar of Jornak
Jornak
Flag of Canada image

Simply using "<>" as the criteria will evaluate to not blank.
Avatar of IfAnybodyCan
IfAnybodyCan

Using ampersand also works

,"<>&"""
IfAnybodyCan's answer is incorrect (missing a quotation mark). It should be:
"<>"&""

And even then, using that is redundant as it is longer and evaluates to exactly the same thing as:
"<>"
It may be redundant but it is very much not incorrect.  Just tried it all 3 ways and they all work; try testing before putting down someone's solution.  It may be redundant but certainly not incorrect :)

The reason i use it is because for some reason the "<>123" hasn't always worked in some versions of excel I've used so I got in the habit of using "<>"&"123"...and just carried the format over for blanks as well.
The answer you provided in your solution was missing a quotation mark / was mistyped and thusly was incorrect. The answer you provided ("<>&""") will result in an incorrect value being displayed. I was only advising the Asker, and correcting your solution, in the case that he or she decided to try your answer to avoid an incorrect value being displayed.

The screenshot below uses your solution as criteria in the formula:
User generated image
As you can see, it returns 13 when the value should clearly be 8.
Avatar of Jenedge73

ASKER

Neither of them work.  I think because i named the criteria field twice and each had a different criteria.   I also tried using a not function SUMIFS($AB$14:$AB$51263,$D$14:$D$51263,"Revenue",$E$14:$E$51263,$E14,$K$14:$K$51263,$K14,$AC$14:$AC$51263,$AC14,$AP$14:$AP$51263,not(or(AP14="SL",AP14=""))=False)
It didn't work so i just
If(or(AP14="SL",Trim(AP14="")=true,0,SUMIFS($AB$14:$AB$51263,$D$14:$D$51263,"Revenue",$E$14:$E$51263,$E14,$K$14:$K$51263,$K14,$AC$14:$AC$51263,$AC14)
But the point is not to change my appraoch but to get my first approach to work.  It feels like i gave up and whent 2nd best.
SOLUTION
Avatar of Jornak
Jornak
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks guys