Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Inbetween Spaces
If I have a column that likes like this:
5 5 6 2 4 2 3 2 3 1 1 2 3 1 3 I want to count each the number of items in each group and have it show up next to it's group as in the example. I assume it's some kind of array formula similar to summing the numbers in each group. Thanks very much for all of the help. I really do appreciate it. This is great group. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Inbetween Spaces
one way you could try, assuming the data begins in cell A1. if not, change
the cell references: =IF(A2="",SUMPRODUCT(--(ROW(A$1:A1)=MAX((A$1:A1="")*(ROW(A$1:A1)),1)),A$ 1:A1),"") entered normally "Peter" wrote: If I have a column that likes like this: 5 5 6 2 4 2 3 2 3 1 1 2 3 1 3 I want to count each the number of items in each group and have it show up next to it's group as in the example. I assume it's some kind of array formula similar to summing the numbers in each group. Thanks very much for all of the help. I really do appreciate it. This is great group. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Inbetween Spaces
One way...
This requires that the cell immediately above the data is empty and the next cell after the last entry is empty. So,assuming your data starts in cell A2 with cell A1 being empty. Enter this formula in B2 and copy down as needed: =IF(A2="","",IF(A3="",COUNT(A2:INDEX(A$1:A1,LOOKUP (2,1/(A$1:A1=""),ROW(A$1:A1)))),"")) -- Biff Microsoft Excel MVP "Peter" wrote in message ... If I have a column that likes like this: 5 5 6 2 4 2 3 2 3 1 1 2 3 1 3 I want to count each the number of items in each group and have it show up next to it's group as in the example. I assume it's some kind of array formula similar to summing the numbers in each group. Thanks very much for all of the help. I really do appreciate it. This is great group. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Inbetween Spaces
Improvement...
This formula doesn't require the cell immediately above the data be empty. Assuming data starts in A2. =IF(A3="",COUNT(A2:INDEX(A$2:A2,MAX(INDEX((A$2:A2= "")*ROW(A$2:A2),,1)))),"") -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... One way... This requires that the cell immediately above the data is empty and the next cell after the last entry is empty. So,assuming your data starts in cell A2 with cell A1 being empty. Enter this formula in B2 and copy down as needed: =IF(A2="","",IF(A3="",COUNT(A2:INDEX(A$1:A1,LOOKUP (2,1/(A$1:A1=""),ROW(A$1:A1)))),"")) -- Biff Microsoft Excel MVP "Peter" wrote in message ... If I have a column that likes like this: 5 5 6 2 4 2 3 2 3 1 1 2 3 1 3 I want to count each the number of items in each group and have it show up next to it's group as in the example. I assume it's some kind of array formula similar to summing the numbers in each group. Thanks very much for all of the help. I really do appreciate it. This is great group. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Inbetween Spaces
Hi and thanks for the formula. I tried dragging it down after
changing the references, but after the first group of numbers it doesn't correctly count from then on. Pilot error? On Fri, 14 Dec 2007 00:05:06 -0500, "T. Valko" wrote: Improvement... This formula doesn't require the cell immediately above the data be empty. Assuming data starts in A2. =IF(A3="",COUNT(A2:INDEX(A$2:A2,MAX(INDEX((A$2:A2 ="")*ROW(A$2:A2),,1)))),"") |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Inbetween Spaces
Works for me. See this screencap:
http://img205.imageshack.us/img205/3...tconsecpt0.jpg after changing the references If it doesn't work tell me where the data is. Is there more than one empty cell between any group? -- Biff Microsoft Excel MVP "Peter" wrote in message ... Hi and thanks for the formula. I tried dragging it down after changing the references, but after the first group of numbers it doesn't correctly count from then on. Pilot error? On Fri, 14 Dec 2007 00:05:06 -0500, "T. Valko" wrote: Improvement... This formula doesn't require the cell immediately above the data be empty. Assuming data starts in A2. =IF(A3="",COUNT(A2:INDEX(A$2:A2,MAX(INDEX((A$2:A 2="")*ROW(A$2:A2),,1)))),"") |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Inbetween Spaces
If your data starts in A2 then put this formula in B2:
=IF(A3="",COUNT(A$2:A2)-SUM(B1:B$2),"") Copy this formula down and you'll get what you need. It's also a little easier to understand than what has been posted previously. g- ___________________________________ Need holiday money. Got any jobs, big or small, drop me a line! Thanks! "Peter" wrote in message ... If I have a column that likes like this: 5 5 6 2 4 2 3 2 3 1 1 2 3 1 3 I want to count each the number of items in each group and have it show up next to it's group as in the example. I assume it's some kind of array formula similar to summing the numbers in each group. Thanks very much for all of the help. I really do appreciate it. This is great group. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Inbetween Spaces
Hi, it works for me to if I insert it into a blank page and start
fresh. In my spreadsheet, no. There is not more than one empty cell between any group. So far I only have three groups. The first one has 14 entries and the next two each have 27, but it says there are 19 for the last two groups. I'm assuming it doesn't make any difference how many columns there are between the data and the count, but in case it did make a difference I just counted a column that was adjacent. Very perplexing. On Fri, 14 Dec 2007 01:09:01 -0500, "T. Valko" wrote: Works for me. See this screencap: http://img205.imageshack.us/img205/3...tconsecpt0.jpg after changing the references If it doesn't work tell me where the data is. Is there more than one empty cell between any group? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Inbetween Spaces
It's also a little easier to understand than what has been posted
previously. Yeah, you got that right! Nice and simple. I'm calling it a day! -- Biff Microsoft Excel MVP "g-" wrote in message ... If your data starts in A2 then put this formula in B2: =IF(A3="",COUNT(A$2:A2)-SUM(B1:B$2),"") Copy this formula down and you'll get what you need. It's also a little easier to understand than what has been posted previously. g- ___________________________________ Need holiday money. Got any jobs, big or small, drop me a line! Thanks! "Peter" wrote in message ... If I have a column that likes like this: 5 5 6 2 4 2 3 2 3 1 1 2 3 1 3 I want to count each the number of items in each group and have it show up next to it's group as in the example. I assume it's some kind of array formula similar to summing the numbers in each group. Thanks very much for all of the help. I really do appreciate it. This is great group. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Inbetween Spaces
See g-'s reply.
Nice and simple! -- Biff Microsoft Excel MVP "Peter" wrote in message ... Hi, it works for me to if I insert it into a blank page and start fresh. In my spreadsheet, no. There is not more than one empty cell between any group. So far I only have three groups. The first one has 14 entries and the next two each have 27, but it says there are 19 for the last two groups. I'm assuming it doesn't make any difference how many columns there are between the data and the count, but in case it did make a difference I just counted a column that was adjacent. Very perplexing. On Fri, 14 Dec 2007 01:09:01 -0500, "T. Valko" wrote: Works for me. See this screencap: http://img205.imageshack.us/img205/3...tconsecpt0.jpg after changing the references If it doesn't work tell me where the data is. Is there more than one empty cell between any group? |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Inbetween Spaces
If your data starts in A2 then put this formula in B2:
=IF(A3="",COUNT(A$2:A2)-SUM(B1:B$2),"") That works just fine as long as the data doesn't start in row 1. -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... It's also a little easier to understand than what has been posted previously. Yeah, you got that right! Nice and simple. I'm calling it a day! -- Biff Microsoft Excel MVP "g-" wrote in message ... If your data starts in A2 then put this formula in B2: =IF(A3="",COUNT(A$2:A2)-SUM(B1:B$2),"") Copy this formula down and you'll get what you need. It's also a little easier to understand than what has been posted previously. g- ___________________________________ Need holiday money. Got any jobs, big or small, drop me a line! Thanks! "Peter" wrote in message ... If I have a column that likes like this: 5 5 6 2 4 2 3 2 3 1 1 2 3 1 3 I want to count each the number of items in each group and have it show up next to it's group as in the example. I assume it's some kind of array formula similar to summing the numbers in each group. Thanks very much for all of the help. I really do appreciate it. This is great group. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting Inbetween Spaces
That worked. Thanks guys.
On Fri, 14 Dec 2007 02:45:23 -0500, "T. Valko" wrote: If your data starts in A2 then put this formula in B2: =IF(A3="",COUNT(A$2:A2)-SUM(B1:B$2),"") That works just fine as long as the data doesn't start in row 1. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I remove leading spaces and leave the remianing spaces w | Excel Worksheet Functions | |||
NEED TO SHOW EACH DAY INBETWEEN TWO DATES, help please | Excel Worksheet Functions | |||
Using IF function for inbetween cell values | Excel Worksheet Functions | |||
How can I add rows inbetween? | Excel Worksheet Functions | |||
How do I enter a row inbetween two rows? | Excel Discussion (Misc queries) |