| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to count occurence of multiple characters in a cell | Excel Worksheet Functions | |||
| count occurence and present results | Excel Worksheet Functions | |||
| Using Sumproduct to count text occurence | Excel Worksheet Functions | |||
| Count the occurence of more than one condition | Excel Worksheet Functions | |||
| formula to count occurence of criteria in 2 columns | Excel Worksheet Functions | |||
Hybrid Mode
