Count by month

[Edmund Seet]

I need Excel to count the number of occurences for the
Type Of Incident by month.

I tried with COUNTIF. It managed to count but I'm lost
from here bcos I don't know how to get Excel to segregate
the count by month.

What is the formula I should put in Sheet2!B2?

Sheet1 contains:
Date Type of Incident
09/03/04 Slow supply
09/07/04 Slow supply
09/10/04 Slow supply
09/14/04 Slow supply
10/01/04 Slow supply
10/15/04 Wrong Issue
11/02/04 Wrong Issue
11/03/04 Slow supply
11/04/04 Slow supply
12/01/04 Slow supply

Sheet2 contains:
Sep-2004 Oct-2004 Nov-2004
Slow supply
Wrong Issue

Thanks in advance

 

[Guest]

Edmund,

Try:

=SUM((MONTH(Sheet1!$A$2:$A$11)=MONTH(B$1))*(YEAR(Sheet1!$A$2:$A$11)=YEAR(B$1))*(Sheet1!$B$2:$B$11=$A2))

Must be array entered (ctrl-shift-enter), then copy to the other cells in
the range on Sheet2.


HTH,
Ryan

 

[Biff]

Hi!

If the headers Sep-2004 Oct-2004 Nov-2004 are formatted
dates and are not just text entries, try this:

=SUMPRODUCT(--(MONTH(Sheet1!$A$2:$A$11)=MONTH(B$1)),--
(Sheet1!$B$2:$B$11=$A2))

Copy across, then down.

Biff

 

[Arvi Laanemets]

Hi

When there are data (A2:B1000) from many years on Sheet1, then for Sep-2004:
=SUMPRODUCT((MONTH(Sheet1!A2:A1000)=9)*(YEAR(Sheet1!A2:A1000=2004)*(Sheet1!B
2:B1000="Slow supply"))
and
=SUMPRODUCT((MONTH(Sheet1!A2:A1000)=9)*(YEAR(Sheet1!A2:A1000=2004)*(Sheet1!B
2:B1000="Wrong Issue"))

Or
=SUMPRODUCT(--(MONTH(Sheet1!A2:A1000)=9),--(YEAR(Sheet1!A2:A1000=2004),--(Sh
eet1!B2:B1000="Slow supply"))
and
=SUMPRODUCT(--(MONTH(Sheet1!A2:A1000)=9),--(YEAR(Sheet1!A2:A1000=2004),--(Sh
eet1!B2:B1000="Wrong Issue"))