how to calculate the sum

  • Thread starter Thread starter smartin
  • Start date Start date
S

smartin

Hi,


How can I have the sum of the sam word use in a row ?

Thanks in advance for your help ..

Stephan
 
You probably need to give us a bit more detail here - Perhaps an example would
help? :-)
 
Stephane,

If you mean to find the occurrences of a word in a row, try

=COUNTIF(1:1,"Stephane")

If there might be other characters in a cell as well as your word, use


=COUNTIF(1:1,"*Stephane*")

ad if there could be more than 1 ocurrence of your word inb a cell and you
want tgo count them all, try

=SUMPRODUCT(LEN(1:1)-LEN(SUBSTITUTE(1:1,"Stephane","")))/LEN("Stephane")

or shorter but array entered

=SUM(LEN(1:1)-LEN(SUBSTITUTE(1:1,"Stephane","")))/LEN("Stephane")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
LOL - Methinks Bob has his helpful hat on, and Ken has his Sarky hat on today -
Xmas spirit is disappearing the closer we get to the 5th :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all :-)
----------------------------------------------------------------------------



Bob Phillips said:
Stephane,

If you mean to find the occurrences of a word in a row, try

=COUNTIF(1:1,"Stephane")

If there might be other characters in a cell as well as your word, use


=COUNTIF(1:1,"*Stephane*")

ad if there could be more than 1 ocurrence of your word inb a cell and you
want tgo count them all, try

=SUMPRODUCT(LEN(1:1)-LEN(SUBSTITUTE(1:1,"Stephane","")))/LEN("Stephane")

or shorter but array entered

=SUM(LEN(1:1)-LEN(SUBSTITUTE(1:1,"Stephane","")))/LEN("Stephane")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
It's all perspective Ken, as we have found countless times before.

Enjoy your last few days of freedom!

Bob

Ken Wright said:
LOL - Methinks Bob has his helpful hat on, and Ken has his Sarky hat on today -
Xmas spirit is disappearing the closer we get to the 5th :-)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

-------------------------------------------------------------------------- --
Seasons Greetings and Very Best wishes to all :-)
-------------------------------------------------------------------------- --
Click to expand...
 
LOL - I'm clinging on to them so tight, it hurts, but they keep slipping away,
slowly but surely, Sob Sob. Hope you had a good one Bob, as mine has been
brilliant. Enjoyed every minute from start to finish.
 
Back
Top