Sumproduct

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

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
 
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>!)
 
Back
Top