Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif with multiple conditions
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif with multiple conditions
I think you'll want sumproduct for this. Try something like this:
=SUMPRODUCT(--(B1:B3000H6),--(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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif with multiple conditions
Thank you very much this worked just fine.
Vito "PCLIVE" wrote: I think you'll want sumproduct for this. Try something like this: =SUMPRODUCT(--(B1:B3000H6),--(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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF with multiple conditions? | Excel Worksheet Functions | |||
COUNTIF with multiple conditions | Excel Discussion (Misc queries) | |||
CountIf with multiple conditions | Excel Worksheet Functions | |||
COUNTIF Multiple Conditions | Excel Discussion (Misc queries) | |||
COUNTIF and multiple conditions | Excel Worksheet Functions |