SOLVED

Minus cells if other cells equal

%3CLINGO-SUB%20id%3D%22lingo-sub-1610436%22%20slang%3D%22en-US%22%3EMinus%20cells%20if%20other%20cells%20equal%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610436%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20do%20the%20following%20and%20it's%20not%20working.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20cells%26nbsp%3BX2%3AX30%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22%26lt%3B%26gt%3BCLSD%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%26nbsp%3BAI2-S2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20formula%20is%20not%20working%3A%3C%2FP%3E%3CP%3E%3DSUMIF(X2%3AX30%2C%22%26lt%3B%26gt%3BCLSD%22%2CAI2-S2)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1610436%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1610610%22%20slang%3D%22en-US%22%3ERe%3A%20Minus%20cells%20if%20other%20cells%20equal%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610610%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F770321%22%20target%3D%22_blank%22%3E%4031moons%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20need%20to%20change%20the%20syntax%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ESUMIF(X2%3AX30%2C%22%26lt%3B%26gt%3B%22%26amp%3BCLSD%2CAI2-S2)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20am%20not%20sure%20what%20you%20have%20in%20each%20cell%20though.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ECheers%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1610639%22%20slang%3D%22en-US%22%3ERe%3A%20Minus%20cells%20if%20other%20cells%20equal%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610639%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F622486%22%20target%3D%22_blank%22%3E%40wumolad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECells%20X2%3AX30%20contain%20text%3C%2FP%3E%3CP%3ECells%20A12%20and%20S2%20contain%20currency%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20try%20to%20minus%20two%20cells%20only%20if%20the%20other%20cell%20contains%20%22CLSD%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20your%20suggestion%20but%20it%20did%20not%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1610709%22%20slang%3D%22en-US%22%3ERe%3A%20Minus%20cells%20if%20other%20cells%20equal%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610709%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F770321%22%20target%3D%22_blank%22%3E%4031moons%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20the%20contents%20of%20the%20cells%20just%20CLSD%20or%20something%20else%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20is%20it%20possible%20to%20have%20CLSD%20and%20other%20texts%20in%20the%20same%20cell%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1610778%22%20slang%3D%22en-US%22%3ERe%3A%20Minus%20cells%20if%20other%20cells%20equal%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610778%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F622486%22%20target%3D%22_blank%22%3E%40wumolad%3C%2FA%3E%26nbsp%3BThere%20is%20no%20other%20content%20than%20CLSD%20in%20the%20cell.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1610881%22%20slang%3D%22en-US%22%3ERe%3A%20Minus%20cells%20if%20other%20cells%20equal%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1610881%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F770321%22%20target%3D%22_blank%22%3E%4031moons%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22public-DraftStyleDefault-block%20public-DraftStyleDefault-ltr%22%3E%3CSPAN%3EI%20got%20it!%20Instead%20of%20doing%20a%20range%20of%20cells.%20I%20focused%20on%20one%20cell%20and%20used%20the%20IF%20function.%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22public-DraftStyleDefault-block%20public-DraftStyleDefault-ltr%22%3E%3CSPAN%3EIF(X2%3D%22CLSD%22%2CAI2-S2%2C0)%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22%22%3E%3CDIV%20class%3D%22public-DraftStyleDefault-block%20public-DraftStyleDefault-ltr%22%3E%3CSPAN%3EThank%20you%20everyone!!!%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1612135%22%20slang%3D%22en-US%22%3ERe%3A%20Minus%20cells%20if%20other%20cells%20equal%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1612135%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F770321%22%20target%3D%22_blank%22%3E%4031moons%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDepends%20on%20your%20needs%2C%20but%20to%20sum%20on%20entire%20range%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20645px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F214710i3F65EE5FEF347ABA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eit%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT((X2%3AX30%3D%22CLSD%22)*(AI2%3AAI30-S2%3AS30))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1613258%22%20slang%3D%22en-US%22%3ERe%3A%20Minus%20cells%20if%20other%20cells%20equal%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1613258%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThis%20worked%20too.%20%26nbsp%3BThank%20you!%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1613461%22%20slang%3D%22en-US%22%3ERe%3A%20Minus%20cells%20if%20other%20cells%20equal%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1613461%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F770321%22%20target%3D%22_blank%22%3E%4031moons%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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
Highlighted

@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

Highlighted

@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.

Highlighted

@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

Highlighted

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

Highlighted
Best Response confirmed by 31moons (Occasional Contributor)
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!!!
Highlighted

@31moons 

Depends on your needs, but to sum on entire range

image.png

it could be

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

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

Highlighted

@31moons , you are welcome