Forum Discussion

Copper Contributor

Aug 25, 2020

Solved

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)

Formulas and Functions

31moons
Aug 25, 2020
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!!!

8 Replies

31moons
Copper Contributor
Aug 25, 2020
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!!!
- SergeiBaklan
  Diamond Contributor
  Aug 25, 2020
  31moons
  
  Depends on your needs, but to sum on entire range
  
  it could be
  
  =SUMPRODUCT((X2:X30="CLSD")*(AI2:AI30-S2:S30))
  - 31moons
    Copper Contributor
    Aug 26, 2020
    SergeiBaklan This worked too. Thank you! Thank you!
wumolad
Steel Contributor
Aug 25, 2020
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
- 31moons
  Copper Contributor
  Aug 25, 2020
  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.
  - wumolad
    Steel Contributor
    Aug 25, 2020
    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