Countif between two dates based on another value

[Bradley Searle]

Hi,

I'm trying to count the number of occurances of a value between two dates
that I specify.

I've done this by using the following formula and it works:

=COUNTIF('Pre-Sales & WIP'!E:E,">="&Analysis!C1)-COUNTIF('Pre-Sales &
WIP'!E:E,">="&Analysis!C2)

The dates are on sheet 'Analysis' in C1 and C2. The dates are in column E
on sheet 'Pre-Sales & WIP'.

Problem - Although this works I want it to only do the count when a value in
column B on sheet 'Pre-Sales & WIP' equals "WIP" (for example).

I added an IF function to the beginning of my formula but this only told it
to perform the calculation if column B contained the word "WIP" anywhere. I
want it to count the values for every occurance of the word "WIP".

eg:

WIP - Between Correct Dates Specfied = Count
(another word) - Between or Not Between Dates Specified = No Count

I do hope this makes sense, any help appreciated!

 

[Pete_UK]

You can only use COUNTIF (or SUMIF) if you have one condition. As you
have more, then try this:

=SUMPRODUCT(('Pre-Sales & WIP'!E1:E100>=Analysis!C1)*('Pre-Sales &
WIP'!E1:E100<=Analysis!C2)*('Pre-Sales & WIP'!B1:B100="WIP"))

Note also that with SUMPRODUCT you can not use full column references
(unless you have Excel 2007), so I have made these refer to 100 rows -
change if you have more. I've assumed start date in C1 and end date in
C2 in the Analysis sheet.

Hope this helps.

Pete

 

[David Biddulph]

=SUMPRODUCT(('Pre-Sales & WIP'!E1:E100>=Analysis!C1)*('Pre-Sales &
WIP'!E1:E100<Analysis!C2)*('Pre-Sales & WIP'!B1:B100="WIP"))

 

[Bradley Searle]

Pete / David - Thank you so much for the quick reply! Your solutions worked
perfectly. Very impressed!

 

[David Biddulph]

Glad to have been able to help.

 

[Pete_UK]

Thanks for feeding back. Slight difference between the two formulae -
mine includes the end date whereas David's doesn't.

Pete

 

[David Biddulph]

Yes. I'd tried to make my formula equivalent (in the date part) to the OP's
original formula.
--
David Biddulph

Thanks for feeding back. Slight difference between the two formulae -
mine includes the end date whereas David's doesn't.

Pete

 

[Pete_UK]

Understood, David - I was just pointing out to the OP that there was a
slight difference between the two formulae.

Pete