how to calculate the sum

[smartin]

Hi,


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

Thanks in advance for your help ..

Stephan

 

[Ken Wright]

You probably need to give us a bit more detail here - Perhaps an example would
help?

 

[Bob Phillips]

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)

 

[Ken Wright]

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

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)

 

[Bob Phillips]

It's all perspective Ken, as we have found countless times before.

Enjoy your last few days of freedom!

Bob

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

 

[Ken Wright]

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.