Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct
Is there a way to count blank cells (within a specific column of data)
excluding the cells found in rows that are not populated? ex. BH REQ_COMPLETION_DATE 04/02/2008 09:00:00 PM 04/07/2008 09:00:00 PM 03/27/2009 12:23:00 AM 04/28/2008 11:00:00 PM 04/07/2008 09:00:00 PM 05/10/2008 11:00:00 PM 04/25/2008 09:00:00 PM 04/17/2008 10:00:00 PM 04/14/2008 09:00:00 PM 05/23/2008 10:00:00 PM 04/18/2008 05:27:00 PM 05/16/2008 08:00:00 PM 04/30/2008 03:00:00 PM I need to count the blank cells so that action can be taken to correct them. When I try the following formula it counts all blank cells including the blanks in rows that are not populated. Hard to explain ... hope I made some sense. The formula I'm using is as follows: =SUMPRODUCT(('DATA (RAW)'!BH2:BH5000="") *('DATA (RAW)'!N2:N5000<"SUPPORT REQUEST") *('DATA (RAW)'!N2:N5000<"WORK ORDER") *('DATA (RAW)'!AQ2:AQ5000<"EMEA") *('DATA (RAW)'!AQ2:AQ5000<"CALA") *('DATA (RAW)'!AQ2:AQ5000<"NA")) Ken |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct
Is there a field that has to be populated for that row to be populated? Maybe
you could just add one more check: =SUMPRODUCT(('DATA (RAW)'!BH2:BH5000="") *('DATA (RAW)'!N2:N5000<"SUPPORT REQUEST") *('DATA (RAW)'!N2:N5000<"WORK ORDER") *('DATA (RAW)'!AQ2:AQ5000<"EMEA") *('DATA (RAW)'!AQ2:AQ5000<"CALA") *('DATA (RAW)'!AQ2:AQ5000<"NA") *('DATA (RAW)'!A2:A5000<"")) (I used column A) (Easy to read and easy to modify <vbg!) Ken wrote: Is there a way to count blank cells (within a specific column of data) excluding the cells found in rows that are not populated? ex. BH REQ_COMPLETION_DATE 04/02/2008 09:00:00 PM 04/07/2008 09:00:00 PM 03/27/2009 12:23:00 AM 04/28/2008 11:00:00 PM 04/07/2008 09:00:00 PM 05/10/2008 11:00:00 PM 04/25/2008 09:00:00 PM 04/17/2008 10:00:00 PM 04/14/2008 09:00:00 PM 05/23/2008 10:00:00 PM 04/18/2008 05:27:00 PM 05/16/2008 08:00:00 PM 04/30/2008 03:00:00 PM I need to count the blank cells so that action can be taken to correct them. When I try the following formula it counts all blank cells including the blanks in rows that are not populated. Hard to explain ... hope I made some sense. The formula I'm using is as follows: =SUMPRODUCT(('DATA (RAW)'!BH2:BH5000="") *('DATA (RAW)'!N2:N5000<"SUPPORT REQUEST") *('DATA (RAW)'!N2:N5000<"WORK ORDER") *('DATA (RAW)'!AQ2:AQ5000<"EMEA") *('DATA (RAW)'!AQ2:AQ5000<"CALA") *('DATA (RAW)'!AQ2:AQ5000<"NA")) Ken -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMPRODUCT except for | New Users to Excel | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumproduct | Excel Discussion (Misc queries) | |||
SUMPRODUCT? | Excel Worksheet Functions | |||
Sumproduct................. | Excel Worksheet Functions |