Excel - Count number of time a text value is used 1

[CharlieMike73]

I am trying to figure out how to count the number of times the string "XML","ORA", or "MSS" appears in a single column.

I was told to try 'sumproduct' but I was unable to figure out how to get this to work and reading Excel help told me that this returns a zero for non-numeric results.

So what function do I use to test for and count the number of times a non-numeric value appears?

Any assistance is appreciated.

 

[SkipVought]

Hi,

How about COUNTIF?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[CharlieMike73]

Excellent, that looks like that might work - thank you for such a fast reply!

 

[firefytr]

You'll either need multiple COUNTIF() functions...

=COUNTIF(A1:A4,"XML")+COUNTIF(A1:A4,"ORA")+COUNTIF(A1:A4,"MSS")

.. or a single SUMPRODUCT() function...

=SUMPRODUCT((A1:A4="XML")+(A1:A4="ORA")+(A1:A4="MSS"))

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[yogia]

Hi Intelliweb:

Or in line with Zack's proposed solution, you may also use ...

=SUMPRODUCT(--(A1:A4={"XML","ORA","MSS"}))

Or

=SUM(COUNTIF(A1:A4,{"XML","ORA","MSS"}))

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com