Formula Question for Excel 2007

[Marilyn]

Hello,

I'm using Excel 2007 to keep track of attendance for classes held on
different days. I'm would like to create a formula that SUMS up the number
of attendees for each day. Is there a function that would SUM values for each
day it encounters? (example: Sum up all the data for each time you see 8/1/08
and then Sum all the data for each time you see 8/2/08).

Thanks,

 

[Roger Govier]

Hi Marylyn

One way
Assuming your dates are in column A
Set the list of dates you want to count in say D1 downward e.g. D1 8/1/08,
D2 8/2/08 etc.
In E1 enter
=COUNTIF(A:A,"="&C1)
Copy down column E for as many entries as you have in column D

 

[Marilyn]

Hello,

Thanks Roger for the wonderful suggestion. I have one question regarding
the formula below. Is it possible to tell the formula to just give me one
count for each time it finds "8/1/08" and then give me one count for each
time it finds "8/2/08" so on and so forth? When I copy the formula down I
get the same count for each time it finds 8/1/08, 8/2/08, 8/3/08 ect......

Thanks,

 

[Roger Govier]

Hi Marilyn

Sorry. Typo!!!
=COUNTIF(A:A,"="&D1)

As long as you have different dates in D1, D2 etc., then you will get
different results in E1, E2 etc. as you copy down (always assuming that the
counts are not equal!!)

 

[Duke Carey]

You can:
1) generate a secondary list that contains just the distinct dates and use
Roger's formula against that list, though still counting the dates in the
primary list, or
2) wrap Roger's formula in an IF() to test if the 8/1/08 value has already
been round in the primary list

It appears as though you are using his formula using only the primary data
list. So, assuming the dates are in column A, use

=IF(COUNTIF(a$1:a1,a1)=0,COUNTIF(A:A,a1),"Already counted")

Copy it down the length of your data. This will count each value the first
time it appears, and will give you legend Already counted whenever it appears
again.