+ 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
    67

    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
    6,980

    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
    67

    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)

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