Jenedge73
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$512 63,"Revenu e",$E$14:$ E$51263,$E 14,$K$14:$ K$51263,$K 14,$AC$14: $AC$51263, $AC14,$AP$ 14:$AP$512 63,"<>SL", $AP$14:$AP $51263,"<> """)
SUMIFS($AB$14:$AB$51263,$D
Simply using "<>" as the criteria will evaluate to not blank.
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:
"<>"
"<>"&""
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 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:
As you can see, it returns 13 when the value should clearly be 8.
The screenshot below uses your solution as criteria in the formula:
As you can see, it returns 13 when the value should clearly be 8.
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$512 63,"Revenu e",$E$14:$ E$51263,$E 14,$K$14:$ K$51263,$K 14,$AC$14: $AC$51263, $AC14,$AP$ 14:$AP$512 63,not(or( AP14="SL", AP14=""))= False)
It didn't work so i just
If(or(AP14="SL",Trim(AP14= "")=true,0 ,SUMIFS($A B$14:$AB$5 1263,$D$14 :$D$51263, "Revenue", $E$14:$E$5 1263,$E14, $K$14:$K$5 1263,$K14, $AC$14:$AC $51263,$AC 14)
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.
It didn't work so i just
If(or(AP14="SL",Trim(AP14=
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys