Multiple countifs between dates

Question

QUESTION: I am using2003.

I have the following formula (on worksheet 2 cell A1)which will count all the entries in range HSEB between the dates in column F2 and G2: =COUNTIF(HSEB,">="&$F$2)-COUNTIF(HSEB,">"&$G$2)

Now I want to count the number of specific entries between these dates. For example I want to count how many times Interruption appears in range HSEB between the two dates. I started with this: =SUM(HSEN="Interruption",COUNTIF(HSEB,">="&$F$2)-COUNTIF(HSEB,">"&$G$2)) and then changed it numerous ways but still can't get it to work including using the examples with VALUE etc that are easily found on the net.

I want the correct formula above on worksheet 2 cell A1 then another one counting "Safety" between the same dates in cell A2.

Would you please be able to help. I'm sure I'm close but just can't get it. Zee

ANSWER: I am confused.

If your date range is defined with the name of HSEB then in which range the"Interruption" is lying? Both are in same range??? that is HSEB is the range for both Date and the"Interruption"?

If you want to count the"Interruption" along with the date in the same range then you can use the formula like the below right.

=(COUNTIF(HSEB,">="&$F$2)-COUNTIF(HSEB,">"&$G$2))+COUNTIF(HSEB,"Interruption")

Instead of the above formula you can use your formula like the below:- =SUMPRODUCT((HSEB>=$F$2)*(HSEB>$G$2))

If the interruption data range is different and it is also having the same row references like the other defined name range then use the formula like the below:- =SUMPRODUCT((DEFNAMERANGETWO="INTERRUPTION")*(HSEB>=$F$2)*(HSEB>$G$2))

Hope it's clear to you!!!

---------- FOLLOW-UP ----------

QUESTION: HSEN = Looking for theinterruption in this column range HSEB = Looking for dates between F2 & G2 in this column range

So if F2 is 01/10/2010 & G2 is 30/10/2010 I want to see how many time "Interruption" appears in the other column during the month of October.

Does that help? Zee

Answer

Have you tried the below formula?

=SUMPRODUCT((HSEN="INTERRUPTION")*(HSEB>=$F$2)*(HSEB<$G$2))

I have created a sample file for you, Click or Copy and paste the below link in web browser to download the Example file.

http://www.sendspace.com/file/mev8zu

Scroll the mouse button to the bottom of the website and click Download

The File Name is: MULTIPLE CRITERIA SOLUTION (04-11-2010).xls

Hope it's Clear to you!