Count cells in one column if they do or do not start with a text in another column.

[JeffFinnan]

I am trying to find a way to add up numbers in one column if the numbers meet the criterium that a text cell in another column does not start with "-". The cells in the other column could be -TTWO230120C115, SWKS, NYT, -NYT220121C55, etc. I want to count cell that contain SWKS and NYT in another column, but not -TTWO230120C115 and -NYT220121C55.

I tried =SUMIF(C2:C103,"<>-*",I2:I103) and all cells were counted. If I tried =SUMIF(C2:C103,"-*",I2:I103), no cells were counted. If I used =SUMIFS(I2:I103,C2:C103,"-*"), got 0 and if "<>-*" in that formula, got all numbers added.

Now my work around was in a separate column use =IF(LEFT(TRIM(C2),1)="-",0,I2) and copy down and then sum that new column. Is there a way to not have to set up a separate column?

Thanks,
Jeff

 

[Fluff]

Check that you don't have a space before the - sign. As

=SUMIFS(I2:I103,C2:C103,"<>-*")

should work.

 

[JeffFinnan]

Check that you don't have a space before the - sign. As

=SUMIFS(I2:I103,C2:C103,"<>-*")

should work.

Still counts all cells.

 

[Fluff]

Did you check to ensure there were no leading spaces (or other characters) in the data?

 

[JeffFinnan]

Did you check to ensure there were no leading spaces (or other characters) in the data?

Yes. Please note that =IF(LEFT(TRIM(C2),1)="-",0,I2) properly detects whether the "-" sign is there or not and gives the correct response, 0 or values depending on the presence or absence of the sign.

Thanks,
Jeff

 

[Fluff]

That suggests that you do have one (or more) spaces before the - sign.
What does

=IF(LEFT(C2,1)="-",0,I2)

return?

 

[JeffFinnan]

That suggests that you do have one (or more) spaces before the - sign.
What does

=IF(LEFT(C2,1)="-",0,I2)

return?

Yeah, it reports all values. I went back and realized that I did not understand what you said about spaces. If I use

=SUMIFS(I2:I103,C2:C103,"<> -*")

with that space before the sign. It works. So problem solved. Sorry about my misunderstanding.

Thanks,
Jeff

 

[Fluff]

Glad you sorted it & thanks for the feedback.