SUMIF Problems with Non-Numeric Data

[BOSMike]

Working with a spreadsheet that contains non-numeric data in a column that I would like to count. But, the count needs to be based on another criteria, example:

Please Login or Register  to view this content.

Have tried SUMIF(B1:B6, "Team 1", A1:A6) but that always results in 0 due to the x's being non-numeric.

Would like to be able to see a total of 2 for the SUMIF for Team 1 and a total of 1 for Team 2 in this example.

Was looking at SUBSTITUTE as part of the formula but when used in the range it keeps giving me a formula error.

[TMS]

Use COUNTIFS to check both columns

Regards, TMS

[dilipandey]

Hi BOSMike,

See the attachment and let us know if this suits.

Regards,
DILIPandey

<click on below 'star' if this helps>

[bebo021999]

May BosMike 'd like to do SUMIF with A column contains numbers stored as Text.
If dilipandey #3 doesn't met, try this in his file in cell E1 instead:

Please Login or Register  to view this content.

[Haseeb Avarakkan]

Since you are on XL2007, you can use COUNTIFS as TMS suggested,

=COUNTIFS(B:B,"team 1",A:A,"x")

You can enter 'team 1' & 'x' in a cell then link those cell to formula instead of hard coding.

[BOSMike]

Thank you so much!

Didn't realize both the SUMPRODUCTS and COUNTIFS could work in this situation. Both solutions are perfect.

Mike

[TMS]

You're welcome.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save