Countif with multiple conditions

[vito]

Hello,
I am analysing data for equipment run time I have the following data
A B F G
H
Unit# Date Run #Batches Date Clean ended Date Clean
Started
12 7/5/6 Formula 7/5/6
8/1/6
25 7/5/6
14 7/6/6
16 7/6/6

The dates for the cleaning are specific to the unit number. What i want to
do is find out the number of runs a unit was run in between cleaning. I am
using
=IF(H6-G6=0,"",COUNTIF(B:B,"<"&H6)-COUNTIF(B:B,"<"&G6))
This works if I seperate the run data by unit# and then use the equation,
but I will be using this as a running file for future use and it already
contains 3000 lines of data. I would like add in an additional condition for
the countif to only use dates for a given unit#. Any help would be
appreciated.

Thanks
Vito

 

[PCLIVE]

I think you'll want sumproduct for this. Try something like this:

=SUMPRODUCT(--(B1:B3000>H6),--(B1:B3000<G6),--(A1:A3000=12))

In this formula, it is looking for criteria of dates that are greater than
H6 and less than G6, and where the unit number is 12 (modify as needed).

HTH,
Paul

 

[vito]

Thank you very much this worked just fine.
Vito

I think you'll want sumproduct for this. Try something like this:

=SUMPRODUCT(--(B1:B3000>H6),--(B1:B3000<G6),--(A1:A3000=12))

In this formula, it is looking for criteria of dates that are greater than
H6 and less than G6, and where the unit number is 12 (modify as needed).

HTH,
Paul