Counting occurences in a list

[Thomas Flake]

If I have a list of dates and states as follows:

a b
1 date result
2 4/1/2005 excellent
3 4/1/2005 excellent
4 4/2/2005 excellent
5 4/3/2005 very good
6 4/4/2005 excellent
7 4/4/2005 good
8 4/4/2005 excellent
9 4/5/2005 excellent
10 4/5/2005 excellent
x y
1 excellent =countif(a1:b5,x1)

This will return the count of the days on which I received an excellent. In
this case 3.

Similarly I can use dcounta

x y z
1 date result count
2 4/5/2005 excellent =dcounta(a1:b10,"excellent",x1:y2)

This will give me a count of the number of times that I received an
excellent on that day. In this case 2.

My question is how do I essentially combine the two functions so that I can
get a count from a list where I have to meet multiple criteria?
In my example, the desired result would be the count of excellents for each
day:

p q
1 4/1/2005 2
2 4/2/2005 1
3 4/3/2005 0
4 4/4/2005 2
5 4/5/2005 2


Thanks in advance for any help.

 

[Dave Peterson]

This sounds like a perfect example for using Data|pivottable.

Select your range
Data|Pivottable...
follow the wizard until you get to a step that has a "Layout" button on it.
Hit that Layout button.

Drag the header for the date field to the Row field
drag the header for Result to the row field
drag the header for Result to to the data field
If you don't see "Count of" in that data field, then double click on it and make
choose "Count of"

If you don't want the subtotals for each date, just double click on that grey
Date box and choose None for subtotals.

You may even want to drag the grey Result box to the right one cell.

It gives a nice table.

If you want to read more about the pivottable stuff, you may want to look at
some links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx