Hi, I'm not very good with formulas, so please forgive me.

I have a huge Excel spreadsheet that looks something like this:

Date 1 Date2 Total of Days
1/9/06 1/11/06 2 days
3/15/06 3/25/06 10 days
3/12/06 3/18/06 6 days
2/9/06 2/15/06 6 days

What we would like to do is create a list in Column 4 that shows how many
days listed in column 3 are the same. For Example, if C3 through C14 have 6
days, we would like to show in a separate column that 11 cells have 6 days,
etc. Also, can the formula automatically update if I remove/add a row?

Given your example, assume the table is in the range A1:C5

=COUNTIF(C2:C5="2 days") will give you the count of "2 days". Changing the
formula to =COUNTIF(C2:C5="10 days") will give you the count of "10 days",
etc.

Dave
--
Brevity is the soul of wit.


"Michelle F." wrote:

Hi, I'm not very good with formulas, so please forgive me.

I have a huge Excel spreadsheet that looks something like this:

Date 1 Date2 Total of Days
1/9/06 1/11/06 2 days
3/15/06 3/25/06 10 days
3/12/06 3/18/06 6 days
2/9/06 2/15/06 6 days

What we would like to do is create a list in Column 4 that shows how many
days listed in column 3 are the same. For Example, if C3 through C14 have 6
days, we would like to show in a separate column that 11 cells have 6 days,
etc. Also, can the formula automatically update if I remove/add a row?

Thank you so much!
Do I have to apply this to each cell or can I apply it to an entire column?

"Dave F" wrote:

Given your example, assume the table is in the range A1:C5

=COUNTIF(C2:C5="2 days") will give you the count of "2 days". Changing the
formula to =COUNTIF(C2:C5="10 days") will give you the count of "10 days",
etc.

Dave
--
Brevity is the soul of wit.


"Michelle F." wrote:

Hi, I'm not very good with formulas, so please forgive me.

I have a huge Excel spreadsheet that looks something like this:

Date 1 Date2 Total of Days
1/9/06 1/11/06 2 days
3/15/06 3/25/06 10 days
3/12/06 3/18/06 6 days
2/9/06 2/15/06 6 days

What we would like to do is create a list in Column 4 that shows how many
days listed in column 3 are the same. For Example, if C3 through C14 have 6
days, we would like to show in a separate column that 11 cells have 6 days,
etc. Also, can the formula automatically update if I remove/add a row?

Just enter the formula in a cell. Since you specify the range in the formula
(the C2:C5 part of the formula, it is applied to the whole column.

Dave
--
Brevity is the soul of wit.


"Michelle F." wrote:

Thank you so much!
Do I have to apply this to each cell or can I apply it to an entire column?

"Dave F" wrote:

Given your example, assume the table is in the range A1:C5

=COUNTIF(C2:C5="2 days") will give you the count of "2 days". Changing the
formula to =COUNTIF(C2:C5="10 days") will give you the count of "10 days",
etc.

Dave
--
Brevity is the soul of wit.


"Michelle F." wrote:

Hi, I'm not very good with formulas, so please forgive me.

I have a huge Excel spreadsheet that looks something like this:

Date 1 Date2 Total of Days
1/9/06 1/11/06 2 days
3/15/06 3/25/06 10 days
3/12/06 3/18/06 6 days
2/9/06 2/15/06 6 days

What we would like to do is create a list in Column 4 that shows how many
days listed in column 3 are the same. For Example, if C3 through C14 have 6
days, we would like to show in a separate column that 11 cells have 6 days,
etc. Also, can the formula automatically update if I remove/add a row?

i think what you really want to know is a count of days 1...n. if you know
the maximum number of days you could have, then you could us IF stmt to place
a "1" in the correct column (corresponding to the days) and then count or
sum. The Countif works, but can be inefficient for what you seem to say you
want.

"Dave F" wrote:

Just enter the formula in a cell. Since you specify the range in the formula
(the C2:C5 part of the formula, it is applied to the whole column.

Dave
--
Brevity is the soul of wit.


"Michelle F." wrote:

Thank you so much!
Do I have to apply this to each cell or can I apply it to an entire column?

"Dave F" wrote:

Given your example, assume the table is in the range A1:C5

=COUNTIF(C2:C5="2 days") will give you the count of "2 days". Changing the
formula to =COUNTIF(C2:C5="10 days") will give you the count of "10 days",
etc.

Dave
--
Brevity is the soul of wit.


"Michelle F." wrote:

Hi, I'm not very good with formulas, so please forgive me.

I have a huge Excel spreadsheet that looks something like this:

Date 1 Date2 Total of Days
1/9/06 1/11/06 2 days
3/15/06 3/25/06 10 days
3/12/06 3/18/06 6 days
2/9/06 2/15/06 6 days

What we would like to do is create a list in Column 4 that shows how many
days listed in column 3 are the same. For Example, if C3 through C14 have 6
days, we would like to show in a separate column that 11 cells have 6 days,
etc. Also, can the formula automatically update if I remove/add a row?

With the suggestions each of you have provided to me, I was able to compile a
formula that worked perfectly.

Thank you both so much.

"reno" wrote:

i think what you really want to know is a count of days 1...n. if you know
the maximum number of days you could have, then you could us IF stmt to place
a "1" in the correct column (corresponding to the days) and then count or
sum. The Countif works, but can be inefficient for what you seem to say you
want.

"Dave F" wrote:

Just enter the formula in a cell. Since you specify the range in the formula
(the C2:C5 part of the formula, it is applied to the whole column.

Dave
--
Brevity is the soul of wit.


"Michelle F." wrote:

Thank you so much!
Do I have to apply this to each cell or can I apply it to an entire column?

"Dave F" wrote:

Given your example, assume the table is in the range A1:C5

=COUNTIF(C2:C5="2 days") will give you the count of "2 days". Changing the
formula to =COUNTIF(C2:C5="10 days") will give you the count of "10 days",
etc.

Dave
--
Brevity is the soul of wit.


"Michelle F." wrote:

Hi, I'm not very good with formulas, so please forgive me.

I have a huge Excel spreadsheet that looks something like this:

Date 1 Date2 Total of Days
1/9/06 1/11/06 2 days
3/15/06 3/25/06 10 days
3/12/06 3/18/06 6 days
2/9/06 2/15/06 6 days

What we would like to do is create a list in Column 4 that shows how many
days listed in column 3 are the same. For Example, if C3 through C14 have 6
days, we would like to show in a separate column that 11 cells have 6 days,
etc. Also, can the formula automatically update if I remove/add a row?

Hi Michelle

Another way of tackling this would be to create a range of cells in D1
to D10 with the values 1, 2, 3 etc, up to 10
In cell E1 enter
=SUMPRODUCT(--($B$2:$B$1000-$A$2:$A$1000=$D1))
Copy down through cells E2:E10

Extend the ranges to include your full set of data, but ensure they are
of equal length.
You can change the values in D1 to D2 to whatever intervals you want to
look at, our extend it further and copy the formula down.
--
Regards

Roger Govier


"Michelle F." wrote in message
...
Thank you so much!
Do I have to apply this to each cell or can I apply it to an entire
column?

"Dave F" wrote:

Given your example, assume the table is in the range A1:C5

=COUNTIF(C2:C5="2 days") will give you the count of "2 days".
Changing the
formula to =COUNTIF(C2:C5="10 days") will give you the count of "10
days",
etc.

Dave
--
Brevity is the soul of wit.


"Michelle F." wrote:

Hi, I'm not very good with formulas, so please forgive me.

I have a huge Excel spreadsheet that looks something like this:

Date 1 Date2 Total of Days
1/9/06 1/11/06 2 days
3/15/06 3/25/06 10 days
3/12/06 3/18/06 6 days
2/9/06 2/15/06 6 days

What we would like to do is create a list in Column 4 that shows
how many
days listed in column 3 are the same. For Example, if C3 through
C14 have 6
days, we would like to show in a separate column that 11 cells have
6 days,
etc. Also, can the formula automatically update if I remove/add a
row?