Formula to count occurence between two rows
 

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?

Formula to count occurence between two rows
 

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

"John Blackwell" wrote:

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?

Formula to count occurence between two rows
 

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?

Formula to count occurence between two rows
 

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?

"Mike H" wrote:

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

"John Blackwell" wrote:

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?

Formula to count occurence between two rows
 

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


"John Blackwell" wrote in message
...
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?

"Mike H" wrote:

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

"John Blackwell" wrote:

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?

Formula to count occurence between two rows
 

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?

Formula to count occurence between two rows
 

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?

Formula to count occurence between two rows
 

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?

Formula to count occurence between two rows
 

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?