Calcuting sum of specific word "P" within specific cell range (A5:

[billy liddel]

Sir,
I want to sum specific word in a row. What is the formula. for example: I
want to calculate sum of word 'P' typed in cell between row (A5:Ai5) in AJ
row. what will be formula. Can I have to use IF command & is if tell me How?
Exact formula.

Thanks
Upendra

 

[Mike H]

Hi,

Try this ARRAY formula

=SUM(LEN(A5:AI5)-LEN(SUBSTITUTE(A5:AI5,"P","")))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[Mike H]

Hmmm,

I made it too complicated. Try this instead

=SUMPRODUCT(--NOT(ISERROR(FIND("P",A5:AI5))))

or for non case sensitive

=SUMPRODUCT(--NOT(ISERROR(SEARCH("P",A5:AI5))))

Mike

 

[Ron Rosenfeld]

On Sat, 26 Sep 2009 01:08:02 -0700, billy liddel <billy

Sir,
I want to sum specific word in a row. What is the formula. for example: I
want to calculate sum of word 'P' typed in cell between row (A5:Ai5) in AJ
row. what will be formula. Can I have to use IF command & is if tell me How?
Exact formula.

Thanks
Upendra

If there will be no more than one "P" to be counted in each cell, and if upper
v lower case is irrelevant, then:

=COUNTIF(A5:A15,"*P*")

If case-sensitive, and counting the total number of "P" per cell, then:

=SUMPRODUCT(LEN(A5:A15)-LEN(SUBSTITUTE(A5:A15,"P","")))

If something else, please clarify.
--ron