Count specific to a month

[Jayfaku]

In sheet 1
In column A I have dates
In column B I have drop down list - FAI, MTI, LTI, RO
In column C I have details
Column D.....

In Sheet 2 I want:

Column D count number of FAI (row4),MTI (r5), LTI (r6) for a particular month based on the dates from Sheet 1 A:A

 

[jorismoerings]

Hi,

Try this and see if this helps:

Book1
	C	D	E
3		1	2
4	FAI	4	6
5	MTI	4	4
6	LTI	4	1
Sheet2

Cell Formulas
Range	Formula
D4	=SUMPRODUCT((Sheet1!$B$2:$B$31=Sheet2!$C4)*(MONTH(Sheet1!$A$2:$A$31)=D$3))

 

[Jayfaku]

Thank you for the response.
I am having difficulty with formula
Sheet1!$B$2:$B$31 this is the column with the entries FAI, LTI, MTI, RO
=Sheet2!$C4 this is what I am counting say FAI
Sheet1!$A$2:$A$ this is the column with dates
=D$3 What does this cell reference, is it the month

I tried using your formula with the following but it came up with error

=SUMPRODUCT(('Injury Register'!$I$2:$I$41=Dashboard!A$12)*(MONTH('Injury Register'!$C$2:$C$50)=B$10))

A$12 is FAI
B$10 is July

Thanks

 

[jorismoerings]

Hi,

Formula was incorrect copied.

try this and make sure evaluation and count ranges are of an equal size:

=SUMPRODUCT(('Injury Register'!$I$2:$I$41=Dashboard!A$12)*(MONTH('Injury Register'!$C$2:$C$50)=B$10))

Ranges should containe an equal number of rows so either

1. change the range within column I to 'Injury Register'!$I$2:$I$50 formula looks like this =SUMPRODUCT(('Injury Register'!$I$2:$I$50=Dashboard!A$12)*(MONTH('Injury Register'!$C$2:$C$50)=B$10)); OR
2. change the range within column C down to $C$2:$C$41 and formula becomes =SUMPRODUCT(('Injury Register'!$I$2:$I$41=Dashboard!A$12)*(MONTH('Injury Register'!$C$2:$C$41)=B$10))