Countif NOT

brian thompson3001 via OfficeKB.com · Mar 8, 2006

hi

how do i enter to count if not the letter "H" in a range. All will be
letters in the cells

thanks

Bob Umlas · Mar 8, 2006

=COUNTIF(A1:A100,"<>H")

Domenic · Mar 8, 2006

Try...

=COUNTIF(A2:A100,"?*")-COUNTIF(A2:A100,"H")

Hope this helps!

brian thompson3001 via OfficeKB.com · Mar 8, 2006

only thing is it counts the blank cell, and i want to count only if populated
other than "H"

regards

Max · Mar 8, 2006

Try: =SUMPRODUCT((A1:A100<>"H")*(A1:A100<>""))

---

Max · Mar 8, 2006

only thing is it counts the blank cell, and i want to count only if

Try: =SUMPRODUCT((A1:A100<>"H")*(A1:A100<>""))

The earlier suggestion assumes case sensitivity is not an issue.
If it is, try:
=SUMPRODUCT((NOT(ISNUMBER(FIND("H",A1:A100)))*(A1:A100<>"")))

---

brian thompson3001 via OfficeKB.com · Mar 10, 2006

thanks to you all

I used Max in the end

regards

The earlier suggestion assumes case sensitivity is not an issue.
If it is, try:
=SUMPRODUCT((NOT(ISNUMBER(FIND("H",A1:A100)))*(A1:A100<>"")))

---

Max · Mar 10, 2006

You're welcome (from us) !
Thanks for posting back ..