Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Consecutive Cells
This works great: In Cell G2: =MATCH("Off",A4:E4,0)-1
ColA ColB ColC ColD ColE ColG Apples Apples Off Apples Off 2 Bannanas Bannanas Bannanas Bannanas Off 4 Coyotes Coyotes Coyotes Off Off 3 Elephants Elephants Elephants Elephants Off 4 Now someone please tell me what formula would work in ColG in the set below, with ColG referencing ColA? If possible? ColA ColB ColC ColD ColE ColG Apples Off Coyotes Apples Off 2 Bannanas Apples Elephants Bannanas Off 1 Coyotes Coyotes Off Off Off 3 Elephants Elephants Off Elephants Off 4 just curious if this is possible... thanks.... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Consecutive Cells
assuming the last one should be 2 not 4
=COUNTIF(A2:E2,"Off") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SteveC" wrote in message ... This works great: In Cell G2: =MATCH("Off",A4:E4,0)-1 ColA ColB ColC ColD ColE ColG Apples Apples Off Apples Off 2 Bannanas Bannanas Bannanas Bannanas Off 4 Coyotes Coyotes Coyotes Off Off 3 Elephants Elephants Elephants Elephants Off 4 Now someone please tell me what formula would work in ColG in the set below, with ColG referencing ColA? If possible? ColA ColB ColC ColD ColE ColG Apples Off Coyotes Apples Off 2 Bannanas Apples Elephants Bannanas Off 1 Coyotes Coyotes Off Off Off 3 Elephants Elephants Off Elephants Off 4 just curious if this is possible... thanks.... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Consecutive Cells
Hi, thanks for your response. Looks like that formula is counting "off", but
what I really want is to count whatever is in cell A2 in the range A2:E5, then return that value in G2. Then consecutively count whatever is in cell A3 in the range A2:E5, and return that value in G3... etc... thanks very much for your time... regards, SteveC "Bob Phillips" wrote: assuming the last one should be 2 not 4 =COUNTIF(A2:E2,"Off") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SteveC" wrote in message ... This works great: In Cell G2: =MATCH("Off",A4:E4,0)-1 ColA ColB ColC ColD ColE ColG Apples Apples Off Apples Off 2 Bannanas Bannanas Bannanas Bannanas Off 4 Coyotes Coyotes Coyotes Off Off 3 Elephants Elephants Elephants Elephants Off 4 Now someone please tell me what formula would work in ColG in the set below, with ColG referencing ColA? If possible? ColA ColB ColC ColD ColE ColG Apples Off Coyotes Apples Off 2 Bannanas Apples Elephants Bannanas Off 1 Coyotes Coyotes Off Off Off 3 Elephants Elephants Off Elephants Off 4 just curious if this is possible... thanks.... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Consecutive Cells
OK, so why is G3, looking for Bananas, only 1, not 2
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SteveC" wrote in message ... Hi, thanks for your response. Looks like that formula is counting "off", but what I really want is to count whatever is in cell A2 in the range A2:E5, then return that value in G2. Then consecutively count whatever is in cell A3 in the range A2:E5, and return that value in G3... etc... thanks very much for your time... regards, SteveC "Bob Phillips" wrote: assuming the last one should be 2 not 4 =COUNTIF(A2:E2,"Off") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SteveC" wrote in message ... This works great: In Cell G2: =MATCH("Off",A4:E4,0)-1 ColA ColB ColC ColD ColE ColG Apples Apples Off Apples Off 2 Bannanas Bannanas Bannanas Bannanas Off 4 Coyotes Coyotes Coyotes Off Off 3 Elephants Elephants Elephants Elephants Off 4 Now someone please tell me what formula would work in ColG in the set below, with ColG referencing ColA? If possible? ColA ColB ColC ColD ColE ColG Apples Off Coyotes Apples Off 2 Bannanas Apples Elephants Bannanas Off 1 Coyotes Coyotes Off Off Off 3 Elephants Elephants Off Elephants Off 4 just curious if this is possible... thanks.... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Consecutive Cells
I undestand your question now. It's because I only want to count
consecutively, left to right. So if Bannanas is only in Col A and Col D, it only counts 1. E.g. counting X -- X is always in ColA X Y Y Y 1 X Y X X 1 X X Y X 2 X X Y Y 2 X X X X 4 Thanks for taking a look... "Bob Phillips" wrote: OK, so why is G3, looking for Bananas, only 1, not 2 -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SteveC" wrote in message ... Hi, thanks for your response. Looks like that formula is counting "off", but what I really want is to count whatever is in cell A2 in the range A2:E5, then return that value in G2. Then consecutively count whatever is in cell A3 in the range A2:E5, and return that value in G3... etc... thanks very much for your time... regards, SteveC "Bob Phillips" wrote: assuming the last one should be 2 not 4 =COUNTIF(A2:E2,"Off") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SteveC" wrote in message ... This works great: In Cell G2: =MATCH("Off",A4:E4,0)-1 ColA ColB ColC ColD ColE ColG Apples Apples Off Apples Off 2 Bannanas Bannanas Bannanas Bannanas Off 4 Coyotes Coyotes Coyotes Off Off 3 Elephants Elephants Elephants Elephants Off 4 Now someone please tell me what formula would work in ColG in the set below, with ColG referencing ColA? If possible? ColA ColB ColC ColD ColE ColG Apples Off Coyotes Apples Off 2 Bannanas Apples Elephants Bannanas Off 1 Coyotes Coyotes Off Off Off 3 Elephants Elephants Off Elephants Off 4 just curious if this is possible... thanks.... |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Consecutive Cells
.... keeping in mind that the variables that we are matching to Col A are
randomly dispersed or even omitted in Col B, Col C and Col D, and we are counting consecutively left to right... |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Consecutive Cells
Then I am confused where any of these numbers come form
ColA |ColB |ColC |ColD |ColE|ColG Apples |Off |Coyotes |Apples |Off |2 Bannanas |Apples |Elephants|Bannanas|Off |1 Coyotes |Coyotes |Off |Off |Off |3 Elephants|Elephants|Off |Elephants|Off |4 Whys isn't it 1,1,1,1? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SteveC" wrote in message ... ... keeping in mind that the variables that we are matching to Col A are randomly dispersed or even omitted in Col B, Col C and Col D, and we are counting consecutively left to right... |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Consecutive Cells
ColA |ColB |ColC |ColD |ColE |ColG
Apples |Off |Coyotes |Apples |Off |2 Bannanas |Apples |Elephants |Bannanas |Off |1 Coyotes |Coyotes |Off |Off |Off |3 Elephants|Elephants|Off |Elephants |Off |4 Cell G2 looks for the number of consecutive times that anything in range A2:E5 matches A2. In other words: ColA |ColB |ColC |ColD |ColE |ColG Apples | | |Apples |Off |2 |Apples | | |Off | | | | |Off | | | | |Off | Cell G2 is referencing cell A2. The Value "2" equals the number of consecutive times (left to right) that "Apples" appears in range A2:E5. In the case of "Apples" it's not 1, 1, 1, 1 but 1,1, , 1 and we get the value "2" thanks for your patience and help... "Bob Phillips" wrote: Then I am confused where any of these numbers come form ColA |ColB |ColC |ColD |ColE|ColG Apples |Off |Coyotes |Apples |Off |2 Bannanas |Apples |Elephants|Bannanas|Off |1 Coyotes |Coyotes |Off |Off |Off |3 Elephants|Elephants|Off |Elephants|Off |4 Whys isn't it 1,1,1,1? -- HTH Bob Phillips |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Consecutive Cells
Sorry, but I am still not getting this.
Are you saying that Apples should be two because column A contains an Apple, as does column B (irrespective of the fact that they are in different rows)? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SteveC" wrote in message ... ColA |ColB |ColC |ColD |ColE |ColG Apples |Off |Coyotes |Apples |Off |2 Bannanas |Apples |Elephants |Bannanas |Off |1 Coyotes |Coyotes |Off |Off |Off |3 Elephants|Elephants|Off |Elephants |Off |4 Cell G2 looks for the number of consecutive times that anything in range A2:E5 matches A2. In other words: ColA |ColB |ColC |ColD |ColE |ColG Apples | | |Apples |Off |2 |Apples | | |Off | | | | |Off | | | | |Off | Cell G2 is referencing cell A2. The Value "2" equals the number of consecutive times (left to right) that "Apples" appears in range A2:E5. In the case of "Apples" it's not 1, 1, 1, 1 but 1,1, , 1 and we get the value "2" thanks for your patience and help... "Bob Phillips" wrote: Then I am confused where any of these numbers come form ColA |ColB |ColC |ColD |ColE|ColG Apples |Off |Coyotes |Apples |Off |2 Bannanas |Apples |Elephants|Bannanas|Off |1 Coyotes |Coyotes |Off |Off |Off |3 Elephants|Elephants|Off |Elephants|Off |4 Whys isn't it 1,1,1,1? -- HTH Bob Phillips |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Consecutive Cells
Thanks for trying to figure this out.
Forget everything I just said. Let's start begin this way: Example 1: Count the number of times A is in the series A1:E5. You should Count 5. Col A Col B Col C Col D Col E Row1 A A Row2 A Row3 A Row4 A Row5 Example 2: Now Count the number of consecutive times A appears in the series, starting from ColA and finishing at ColE. The key word is "consecutive." You should could count 2. This is because there is no "A" in Col C. The formula should stop counting everything after the break in the series. Col A Col B Col C Col D Col E Row1 A Row2 A Row3 A Row4 A Row5 My question is, what formula in G1 will count the consecutive number of "As" in the series A1:E5. That is, what formula will return a value of 2. Thanks for taking a look... (this is easily solvable via a sorting macro or multiple vlookups in A1:E5, but I wanted to see if it's possible in the way described above...) |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Consecutive Cells
I'm not sure this is what you're looking for, but try the following...
Insert Name Define Name: BigNum Refers to: =9.99999999999999E+307 Click Ok Then, try... =LOOKUP(BigNum,CHOOSE({1,2},COLUMNS(A1:E5),MATCH(0 ,MMULT({1,1,1,1,1},--(A 1:E5="A")),0)-1)) In article , SteveC wrote: Thanks for trying to figure this out. Forget everything I just said. Let's start begin this way: Example 1: Count the number of times A is in the series A1:E5. You should Count 5. Col A Col B Col C Col D Col E Row1 A A Row2 A Row3 A Row4 A Row5 Example 2: Now Count the number of consecutive times A appears in the series, starting from ColA and finishing at ColE. The key word is "consecutive." You should could count 2. This is because there is no "A" in Col C. The formula should stop counting everything after the break in the series. Col A Col B Col C Col D Col E Row1 A Row2 A Row3 A Row4 A Row5 My question is, what formula in G1 will count the consecutive number of "As" in the series A1:E5. That is, what formula will return a value of 2. Thanks for taking a look... (this is easily solvable via a sorting macro or multiple vlookups in A1:E5, but I wanted to see if it's possible in the way described above...) |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Consecutive Cells
Make that...
=LOOKUP(BigNum,CHOOSE({1,2},COLUMNS(A1:E5),MATCH(0 ,MMULT(COLUMN(A1:E5)^0, --(A1:E5="A")),0)-1)) In article , Domenic wrote: I'm not sure this is what you're looking for, but try the following... Insert Name Define Name: BigNum Refers to: =9.99999999999999E+307 Click Ok Then, try... =LOOKUP(BigNum,CHOOSE({1,2},COLUMNS(A1:E5),MATCH(0 ,MMULT({1,1,1,1,1},--(A 1:E5="A")),0)-1)) In article , SteveC wrote: Thanks for trying to figure this out. Forget everything I just said. Let's start begin this way: Example 1: Count the number of times A is in the series A1:E5. You should Count 5. Col A Col B Col C Col D Col E Row1 A A Row2 A Row3 A Row4 A Row5 Example 2: Now Count the number of consecutive times A appears in the series, starting from ColA and finishing at ColE. The key word is "consecutive." You should could count 2. This is because there is no "A" in Col C. The formula should stop counting everything after the break in the series. Col A Col B Col C Col D Col E Row1 A Row2 A Row3 A Row4 A Row5 My question is, what formula in G1 will count the consecutive number of "As" in the series A1:E5. That is, what formula will return a value of 2. Thanks for taking a look... (this is easily solvable via a sorting macro or multiple vlookups in A1:E5, but I wanted to see if it's possible in the way described above...) |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Consecutive Cells
Domenic, thanks! That works if I input "A" anywhere in the range A1:E5.
Is there a way to make it work for any text value? It doesn't work with "B" or "Apples", for example. Thanks very much! SteveC "Domenic" wrote: Make that... =LOOKUP(BigNum,CHOOSE({1,2},COLUMNS(A1:E5),MATCH(0 ,MMULT(COLUMN(A1:E5)^0, --(A1:E5="A")),0)-1)) In article , Domenic wrote: I'm not sure this is what you're looking for, but try the following... Insert Name Define Name: BigNum Refers to: =9.99999999999999E+307 Click Ok Then, try... =LOOKUP(BigNum,CHOOSE({1,2},COLUMNS(A1:E5),MATCH(0 ,MMULT({1,1,1,1,1},--(A 1:E5="A")),0)-1)) In article , SteveC wrote: Thanks for trying to figure this out. Forget everything I just said. Let's start begin this way: Example 1: Count the number of times A is in the series A1:E5. You should Count 5. Col A Col B Col C Col D Col E Row1 A A Row2 A Row3 A Row4 A Row5 Example 2: Now Count the number of consecutive times A appears in the series, starting from ColA and finishing at ColE. The key word is "consecutive." You should could count 2. This is because there is no "A" in Col C. The formula should stop counting everything after the break in the series. Col A Col B Col C Col D Col E Row1 A Row2 A Row3 A Row4 A Row5 My question is, what formula in G1 will count the consecutive number of "As" in the series A1:E5. That is, what formula will return a value of 2. Thanks for taking a look... (this is easily solvable via a sorting macro or multiple vlookups in A1:E5, but I wanted to see if it's possible in the way described above...) |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Consecutive Cells
If your data does not contain numerical values, you could simply
replace... ="A" with <"" Is this the case? In article , SteveC wrote: Domenic, thanks! That works if I input "A" anywhere in the range A1:E5. Is there a way to make it work for any text value? It doesn't work with "B" or "Apples", for example. Thanks very much! SteveC |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Consecutive Cells
Ok, that works now for one type of text value
But now when I had more than one text value, it doesn't distinguish between the text values. For example, Cell G22 below should return a value of 2, but it's currently returning a value of 4. ColA | ColB | ColC | Col D | ColE | ColF | Col G| Apples | Bears | Apples | | | | 4 Bears | Apples | | Apples | | | 2 | | | Bears | thanks a lot... |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Consecutive Cells
It seems to me that Columns A through D each have at least one text
value. In this situation, I understood the correct result to be 4. And if we deleted Apples from Column C, leaving Column C with no text values, I understood the correct result to be 2. Isn't this correct? If not, you'll need to clarify... In article , SteveC wrote: Ok, that works now for one type of text value But now when I had more than one text value, it doesn't distinguish between the text values. For example, Cell G22 below should return a value of 2, but it's currently returning a value of 4. ColA | ColB | ColC | Col D | ColE | ColF | Col G| Apples | Bears | Apples | | | | 4 Bears | Apples | | Apples | | | 2 | | | Bears | thanks a lot... |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Consecutive Cells
Yes, that is correct... thanks!
"Domenic" wrote: It seems to me that Columns A through D each have at least one text value. In this situation, I understood the correct result to be 4. And if we deleted Apples from Column C, leaving Column C with no text values, I understood the correct result to be 2. Isn't this correct? If not, you'll need to clarify... In article , SteveC wrote: Ok, that works now for one type of text value But now when I had more than one text value, it doesn't distinguish between the text values. For example, Cell G22 below should return a value of 2, but it's currently returning a value of 4. ColA | ColB | ColC | Col D | ColE | ColF | Col G| Apples | Bears | Apples | | | | 4 Bears | Apples | | Apples | | | 2 | | | Bears | thanks a lot... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for counting number of cells | Excel Discussion (Misc queries) | |||
Counting Occurrence of Text within Text in Cells in Range. | Excel Worksheet Functions | |||
Counting cells with same info in them | Excel Discussion (Misc queries) | |||
Count Consecutive Cells | Excel Discussion (Misc queries) | |||
counting cells with conditional formatting applied | Excel Discussion (Misc queries) |