Formula to count occurence between two rows

[John Blackwell]

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?

 

[Mike H]

Hi,

I can't go to KK17 because I don't have Excel 2007 so change your range and
this should do it

=COUNTIF(D17:IV17,"SLS")

Change the SLS to each of your values

Mike

 

[Gaurav]

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"),COUNTIF(D18:KK18,"MS"),COUNTIF(D18:KK18,"EV"))

Is this what you want?

 

[John Blackwell]

Hi Mike,

Thanks for the really quick response but unfortunately the formula you've
suggested doesn't solve my issue.

Row 17 has the values and row 18 has the number of occurences. For example,
'SLS' occurs in D18, J18, X18, etc etc. In row 18, the number of occurences
is listed. For example, D17=8, J17=14, X17=24, etc etc.

What I'm trying to achieve is the sum total of D17, J17, X17 (i.e. the sum
of 8+14+24), etc etc when 'SLS' occurs in row 18.

Does this make sense?

 

[Gaurav]

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

Hi Mike,

Thanks for the really quick response but unfortunately the formula you've
suggested doesn't solve my issue.

Row 17 has the values and row 18 has the number of occurences. For
example,
'SLS' occurs in D18, J18, X18, etc etc. In row 18, the number of
occurences
is listed. For example, D17=8, J17=14, X17=24, etc etc.

What I'm trying to achieve is the sum total of D17, J17, X17 (i.e. the sum
of 8+14+24), etc etc when 'SLS' occurs in row 18.

Does this make sense?

 

[John Blackwell]

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]

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

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

Does this help now?

 

[John Blackwell]

Gaurav,

Brilliant, thank you - thank you

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

 

[Gaurav]

Glad it worked!

Gaurav,

Brilliant, thank you - thank you

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