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

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