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

  • Thread starter Thread starter billy liddel
  • Start date Start date
B

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
 
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
 
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
 
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
Click to expand...

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
 
Back
Top