COUNTIF for multiple criteria with date range

**pjlau** · 05-04-2012, 06:07 AM

Hi,

I would like to your kind assistance on the following

My worksheet has multiple columns

Column F - date (format dd-mmm)
Column K - product name e.g. PIL, DCPIL etc
Column X - status =1

Column F Column K Column X
1-Mar PIL 1
3-Mar DCPIL 1
15-Mar PIL 1
20-Mar PIL 1
30-Mar DCPIL 1
2Apr PIL 1

Can anyone help to advise the formula (is it possible to use COUNTIF) on how to count the number of status = "1" for the period (1 Mar-15 Mar) with product name "PIL"
The answer I want should be "2"

Thanks

Pjlau

**dilipandey** · 05-04-2012, 06:14 AM

Hi pjlau,

You can use the below formula:-

{=SUM(--($A$1:$A$6>=A1)*($A$1:$A$6<=A3)*($B$1:$B$6=B1))}

It's an arrray formula and need to be entered using key combination Ctrl Shift Enter.

where A1 is 01 Mar and A3 is 15 Mar, B1 = PIL...

Regards,
DILIPandey

<click on below 'star' if this helps>

**Fotis1991** · 05-04-2012, 06:16 AM

Hi

As you are in Excell 2003, try to use SUMPRODUCT function.

If you can not handle this, then upload a small sample workbook to show you the way to do this.

**pjlau** · 05-04-2012, 06:45 AM

Thx.

Sorry I am new to Excel.

As you said "where A1 is 01 Mar and A3 is 15 Mar" , how to define if the period is Apr?

Thanks /pjlau

**Fotis1991** · 05-04-2012, 07:04 AM

Try also SUMPRODUCT

In H1, type your start day

In I1, type your End Date.

Then use this formula.

=SUMPRODUCT(($K$1:$K$1000="pil")*($F$1:$F$1000>=H1)*($F$1:$F$1000<=I1))

Same way for dilipandey's Formula