Count based on multiple conditions (between two dates)

  • Thread starter Thread starter Joe Miller
  • Start date Start date
J

Joe Miller

Hello,

I wonder if anyone can help me on this Excel 2000 problem.

I want to perform a count of data in a worksheet based on
two conditions. Condition one is when cells in column C
contain "E". The second condition is where the cells in
column H (which all contain dd/mm/yyy data) are within a
certain month (i.e in May or between 01/05/2002 and
31/05/2002, whihcever way of looking at it is the easier)

The multiple condition array formulas just dont seem to
work!

Thanks for your help,

Joe
 
Joe,

SUMPRODUCT does it nicely
=SUMPRODUCT((C1:C100="E")*(TEXT(H1:H100,"mmm")="May"))

I have used this form of checking the month as it is clear what is being
tested, but you could also use
=SUMPRODUCT((C1:C10="E")*(MONTH(H1:H10)=5))
which is probably more efficient as it doesn't do string compares.
 
Joe Miller said:
Hello,

I wonder if anyone can help me on this Excel 2000 problem.

I want to perform a count of data in a worksheet based on
two conditions. Condition one is when cells in column C
contain "E". The second condition is where the cells in
column H (which all contain dd/mm/yyy data) are within a
certain month (i.e in May or between 01/05/2002 and
31/05/2002, whihcever way of looking at it is the easier)

The multiple condition array formulas just dont seem to
work!

Thanks for your help,

Joe
Click to expand...

Try something like
=SUMPRODUCT((C1:C99="E")*(MONTH(H1:H99)=5))
Adjust ranges to suit your data, but keep them the same length as each
other.

You may need to include a year condition if your data covers more than one
year:
=SUMPRODUCT((C1:C99="E")*(MONTH(H1:H99)=5)*(YEAR(H1:H99)=2002))
 
=SUMPRODUCT((C1:C100="E")*(MONTH(H1:H100)=5))

It is hard for us to figure out why you couldn't get multiple condition
array formulas to work since you didn't post the formulas you tried.
Possibly you tried to use C:C and H:H? Array formulas cannot reference
an entire column.

Jerry
 
Back
Top