About Us

Gaurav[_3_]

Glad it worked!

"John Blackwell" wrote in message
...
Gaurav,

Brilliant, thank you - thank you :)

I hadn't spotted the row 17 vs 18 issue either.

"Gaurav" wrote:

Sorry...I thought you had SLS etc in row 17.

=SUMPRODUCT((D18:KK18="SLS")*(D17:KK17))

Does this help now?

"John Blackwell" wrote in
message
...
Hi Gaurav,

Thanks for the two ideas but neither achieve the result I'm looking
for.

I was really optimistic your formula
=SUMPRODUCT((D17:KK17="SLS")*(D18:KK18)) would work, but it just comes
back
with #VALUE!

Your second longer formula just sums the overall number of each
occurence
(i.e. SLS, SLM, etc) in row 18

My scenario is that in row 18, each cell between D18:KK18 has entries
such
as SLS, SLM, MLS, MLM, AH, FH, MS, EV and in row 17, each cell between
D17:KK17 has a numeric count. For example, D18 is SLS and D17 tells me
there
are 8 occurences. Similarly J18 is SLM and J17 tells me there are 14
occurences.

What I'm trying to achieve is how many SLS's there are? I then want to
repeat this for each other value (SLM, MLS, MLM, AH, FH, MS, EV).

Does this make the issue clearer? Please let me know - if not, I'll
have
another stab at explaining the issue.

"Gaurav" wrote:

If I understood you correctly.

=SUM(COUNTIF(D18:KK18,"SLS"),COUNTIF(D18:KK18,"SLM "),COUNTIF(D18:KK18,"MLS"),COUNTIF(D18:KK18,"MLM") ,COUNTIF(D18:KK18,"AH"),COUNTIF(D18:KK18,"FH"),COU NTIF(D18:KK18,"MS"),COUNTIF(D18:KK18,"EV"))

Is this what you want?

"John Blackwell" wrote in
message
...
Can anyone help with this formula problem.

I'm working on a large workbook and in row D18:KK18 I have a series
of
eight
different values (i.e. SLS, SLM, MLS, MLM, AH, FH, MS, EV). In row
D17:KK17
I have the number of occurence of these values.

Is there a CountIf (or other) formula I can use that will recognise
the
occurences of each value in row 18 and sum the number of occurences
in
row
17?

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to count occurence of multiple characters in a cell	MLK	Excel Worksheet Functions	4	March 9th 07 12:51 AM
count occurence and present results	Hobbes2006	Excel Worksheet Functions	2	August 7th 06 09:42 PM
Using Sumproduct to count text occurence	Dave	Excel Worksheet Functions	1	July 25th 06 06:15 PM
Count the occurence of more than one condition	wutang1105	Excel Worksheet Functions	1	August 26th 05 07:21 PM
formula to count occurence of criteria in 2 columns	needs help	Excel Worksheet Functions	2	July 27th 05 09:17 PM

Menu

About Us