+ Reply to Thread
Results 1 to 3 of 3

SUMPRODUCT with date criteria that ignores blank cells

  1. #1
    Registered User
    Join Date
    11-18-2014
    Location
    England
    MS-Off Ver
    2016
    Posts
    74

    SUMPRODUCT with date criteria that ignores blank cells

    Hi, I have the following formula which counts calls made by a specific agent in a specific month (agent name and month are selected from dropdowns)

    =SUMPRODUCT(--(MONTH(Historic_Call_List!$A$2:$A$4999)=$J$2),--(Historic_Call_List!$A$2:$A$4999<>""),--(Historic_Call_List!$C$2:$C$4999),--(Historic_Call_List!$H$2:$H$4999=Tables!B1))

    in the data
    Column A contains the dates
    J2 contains the month number I am looking for in the data
    column C2 contains call data
    column H contains agent names
    B1 contains the name of the agent I am looking for in the data

    my problem is column C has some blank cells in so how do I get this formula to count only rows where there is data in column A,C and H and ignore it if there is a blank cell in column C?

    Thanks

    J

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,003

    Re: SUMPRODUCT with date criteria that ignores blank cells

    Does this work (untested)

    =SUMPRODUCT(--(MONTH(Historic_Call_List!$A$2:$A$4999)=$J$2),--(Historic_Call_List!$A$2:$A$4999<>""),--(Historic_Call_List!$C$2:$C$4999<>""),--(Historic_Call_List!$H$2:$H$4999=Tables!B1))
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    11-18-2014
    Location
    England
    MS-Off Ver
    2016
    Posts
    74

    Re: SUMPRODUCT with date criteria that ignores blank cells

    Hi Special-K - yes this works perfectly thank you. should have worked this out given that I already had <>"" in a different part of the formula!

    Thanks

    J

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] conditional formatting that ignores 'blank' cells
    By jimboryan in forum Excel General
    Replies: 3
    Last Post: 11-14-2018, 07:27 PM
  2. How to create a chart that ignores blank cells?
    By Mr.Castle in forum Excel General
    Replies: 4
    Last Post: 09-11-2018, 12:25 PM
  3. [SOLVED] Nested IF formula that ignores blank cells
    By si.mon in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-22-2018, 06:19 AM
  4. [SOLVED] Using concatenate that ignores blank cells and also includes line breaks
    By JohnW12 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-02-2016, 05:09 AM
  5. [SOLVED] Multiple SUMIFS based on criteria but ignores if a cell is blank..
    By Andrewbutler in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 11-23-2014, 06:30 PM
  6. Replies: 2
    Last Post: 07-11-2012, 08:58 AM
  7. Data Validation List That Ignores The Blank Cells
    By nevi in forum Excel General
    Replies: 4
    Last Post: 06-12-2008, 03:54 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1