CODEWITHSUNDEEP
excel
Mohamed Maher
Mohamed Maher

Reputation: 1

Sum if returns 0 when criteria has an operator referring to a cell and works if cell value is entered manually

I am using sum if function and if the operator in the criteria refers to a cell it returns zero and if the cell value is entered manually inside the criteria it works just fine

=SUMIF($AC$9:$AC$25,">=AB9",$AE$9:$AE$25)

doesn't work and works if like this

=SUMIF($AC$9:$AC$25,">=15.25",$AE$9:$AE$25)

Upvotes: 0

Views: 41

Answers (2)

Mohamed Maher
Mohamed Maher

Reputation: 1

i figured it out finally...we can't use operators like > or < to refer to a cell inside the sum if criteria, but we can trick excel to do it....we can use concatenate to add > or < to the cell value and in the sum if criteria we can refer directly to the new concatenated cell like this :))

=CONCATENATE(">=",AO3,"") which returns >=15.25 in a new cell which is AO3 we then refer to it directly like this =SUMIF($AC$9:$AC$25,AO3,$AE$9:$AE$25)

Cheers :)))

Upvotes: 0

Scott Hunter
Scott Hunter

Reputation: 49896

As shown in https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b, you need to "build" the criteria expression; in your case, replace

">=AB9"

with

">=" & AB9

Upvotes: 1

Related Questions