Count Certain Cells in Specified Range
 

Hello,

I was hoping you guys could help me with the following. The data I am
working with is as follows:

Date Type
1/1/06 1
1/5/06 0
1/23/06 3
2/1/06 0
2/3/06 0
2/14/06 2
3/5/06 1
3/29/06 1

What I'm trying to do in excel is count how many times Type 1 occurs
each month (count of Type 1 in Jan, count of Type 1 in Feb and so on).
Does anyone know of a formula that would be able to do this? Thanks!

Xavier

Count Certain Cells in Specified Range
 

Assuming data is columns A & B

=SUMPRODUCT(--(Month(A1:A100)=1),--(B1:B100)=1))

=SUMPRODUCT(--(Month(A1:A100)=3),--(B1:B100)=1))
for March

HTH

"Xavier" wrote:

Hello,

I was hoping you guys could help me with the following. The data I am
working with is as follows:

Date Type
1/1/06 1
1/5/06 0
1/23/06 3
2/1/06 0
2/3/06 0
2/14/06 2
3/5/06 1
3/29/06 1

What I'm trying to do in excel is count how many times Type 1 occurs
each month (count of Type 1 in Jan, count of Type 1 in Feb and so on).
Does anyone know of a formula that would be able to do this? Thanks!

Xavier

Count Certain Cells in Specified Range
 

=SUMPRODUCT(--(A1:A8=A11)*(--(A1:A8<A12)*(--(B1:B8))))

Assumes

A1:A8 = Data
A11 = 01/01/06 1st Jan
A12 = 02/01/06 1st Feb
B1:B8 = Totals

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=564877

Count Certain Cells in Specified Range
 

Thanks guys, got it working!

Xavier

VBA Noob wrote:
=SUMPRODUCT(--(A1:A8=A11)*(--(A1:A8<A12)*(--(B1:B8))))

Assumes

A1:A8 = Data
A11 = 01/01/06 1st Jan
A12 = 02/01/06 1st Feb
B1:B8 = Totals

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=564877