Minus cells if other cells equal

[31moons]

I'm trying do the following and it's not working.

If cells X2:X30

"<>CLSD"

Then AI2-S2

This formula is not working:

=SUMIF(X2:X30,"<>CLSD",AI2-S2)

[#2]

@31moons 

You need to change the syntax

SUMIF(X2:X30,"<>"&CLSD,AI2-S2)

I am not sure what you have in each cell though.

Cheers

[#3]

@wumolad 

Cells X2:X30 contain text

Cells A12 and S2 contain currency 

I try to minus two cells only if the other cell contains "CLSD"

I tried your suggestion but it did not work.

[#4]

@31moons 

Are the contents of the cells just CLSD or something else?

Or is it possible to have CLSD and other texts in the same cell?

Cheers

[#5]

@wumolad There is no other content than CLSD in the cell.

[#6 · Accepted solution]

@31moons 

I got it! Instead of doing a range of cells. I focused on one cell and used the IF function.

IF(X2="CLSD",AI2-S2,0)

Thank you everyone!!!

[#7]

@31moons 

Depends on your needs, but to sum on entire range

it could be

=SUMPRODUCT((X2:X30="CLSD")*(AI2:AI30-S2:S30))

[#8]

@Sergei Baklan This worked too.  Thank you! Thank you!

[#9]

@31moons , you are welcome