Countif with multiple conditions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Thank you very much this worked just fine.
Vito

PCLIVE said:
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
Click to expand...
 
Back
Top