CODEWITHSUNDEEP
excelexcel-2010libreoffice-calc
user3313834
user3313834

Reputation: 7837

excel: make SUM.IF work with formulas

On excel I have a 

=SOMME.SI(B$10:B248;B7;L$10:L248)

B7 contain "a", L$x:L$y has values to sum

If the zone B$10:B248: has "a" or "b" that work very well.

But if the zone B$10:B248: has a formula with an IF: 

SI("zf"=M18;"a";"?")

That did not work any more, how can I explain to excel/calc that 

SI("zf"=M18;"a";"?") should be == "a"

in case the if conditions is true ?

Upvotes: 0

Views: 52

Answers (2)

user4039065
user4039065

Reputation:

The question mark is a wildcard. By referencing it as the criteria, you are telling SUMIFS to accept any single character. You are going to have to change the ? to something else to avoid wildcard matches.

    sumif_wildcard

Upvotes: 1

Mrig
Mrig

Reputation: 11712

If I understood your problem correctly, to get "a" in cell when IF formula returns TRUE, you should use below formula

=SI("zf"=M18;"a";"?")

as

=SI("zf"=M18;"""a""";"?")

You can also use:

=SI(M18="zf",CHAR(34) & "a" & CHAR(34),"?")

Note: CHAR(34) returns the double quote character "

Upvotes: 0

Related Questions