SOLVED

# Minus cells if other cells equal

Occasional Contributor

# Minus cells if other cells equal

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)

8 Replies

# Re: Minus cells if other cells equal

You need to change the syntax

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

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

Cheers

# Re: Minus cells if other cells equal

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.

# Re: Minus cells if other cells equal

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

# Re: Minus cells if other cells equal

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

Best Response confirmed by 31moons (Occasional Contributor)
Solution

# Re: Minus cells if other cells equal

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!!!

# Re: Minus cells if other cells equal

Depends on your needs, but to sum on entire range

it could be

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

# Re: Minus cells if other cells equal

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

# Re: Minus cells if other cells equal

@31moons , you are welcome