+ Reply to Thread
Results 1 to 7 of 7

How to use SUMPRODUCT instead of COUNT IF

  1. #1
    Forum Contributor
    Join Date
    11-18-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    132

    How to use SUMPRODUCT instead of COUNT IF

    Hi, I have a formula
    =COUNTIF('M:\999 Governance\Error Handling\[error handling issues data Jen new version.xlsx]Master sheet'!$Q:$Q,"<=1")

    which counts all the instances where a number in column Q is less than or equal to 1. This works fine. However I am trying to create a dashboard on a different workbook to the one where the data is so I know this formula won't work as countif does not work across workbooks unless both are open which defeats the object of having the dashboard.

    I know sumproduct is the answer and I am convinced I have made it work in this way before but for the life of me I cannot make it work now!

    I would also need to vary the formula so it would do the same but this time where the number was between two values such as 2 and 5

    Please put me out of my misery as I know this can be done!

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How to use SUMPRODUCT instead of COUNT IF

    It's not a good idea to use SUMPRODUCT with full column references (i.e. Q:Q), as it will check through every cell in that column (1 million + of them), whereas COUNTIF is intelligent enough to look only at the used range within that column. So, assuming you only have data up to row 1000, a direct equivalent of that formula would be:

    =SUMPRODUCT(--('M:\999 Governance\Error Handling\[error handling issues data Jen new version.xlsx]Master sheet'!$Q$1:$Q$1000<=1))

    Hope this helps.

    Pete

  3. #3
    Forum Contributor
    Join Date
    11-18-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    132

    Re: How to use SUMPRODUCT instead of COUNT IF

    Hi Pete_UK thanks for the tip re SUMPRODUCT. I have used your version and it does bring back results now the only problem I have is that the column it is counting contains a formula

    =IF(D2=""," ",IF(IFERROR(DAYS360(D2,P2)," ")=0,1,IFERROR(DAYS360(D2,P2)+1," "))) which means that in certain instances the cell looks empty - however the SUMPRODUCT formula appears to count every cell that contains this formula as being <=1?

    Basically I have a table on my dashboard that has the following
    Days to resolve
    1 day
    2-5 days
    6-10 days
    11-40 days
    greater than 40 days

    column Q in the data is the days to resolve column but it is using a formula to calculate the days to resolve between two dates in columns D and P and if there is no resolution date set then the cell remains blank. I need to know how to use the SUMPRODUCT formula to populate all my days to resolve table fields on the dashboard.

    does this make sense and can it be done???

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: How to use SUMPRODUCT instead of COUNT IF

    Try this construct

    =SUMPRODUCT(--(ISNUMBER('Master Sheet'!$Q$1:$Q$10)*('Master Sheet'!$Q$1:$Q$10<1)))

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How to use SUMPRODUCT instead of COUNT IF

    There are 3 instances where that formula is returning a space, rather than a blank - you should use "" rather than " " (I've highlighted in red below, though obviously it is difficult to see the space):

    =IF(D2=""," ",IF(IFERROR(DAYS360(D2,P2)," ")=0,1,IFERROR(DAYS360(D2,P2)+1," ")))

    You could change the main formula to this:

    =SUMPRODUCT(('M:\999 Governance\Error Handling\[error handling issues data Jen new version.xlsx]Master sheet'!$Q$1:$Q$1000<>"")*('M:\999 Governance\Error Handling\[error handling issues data Jen new version.xlsx]Master sheet'!$Q$1:$Q$1000<=1))

    Hope this helps.

    Pete

  6. #6
    Forum Contributor
    Join Date
    11-18-2014
    Location
    England
    MS-Off Ver
    365
    Posts
    132

    Re: How to use SUMPRODUCT instead of COUNT IF

    Hi Pete, that does the trick! Thank you. now how would I do it if I wanted to look for everything that contained a value between say 2 and 5? as that is the next category on my days lookup.

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: How to use SUMPRODUCT instead of COUNT IF

    You now have two conditions, so you need to have another term in the SUMPRODUCT function, like this:

    =SUMPRODUCT(('M:\999 Governance\Error Handling\[error handling issues data Jen new version.xlsx]Master sheet'!$Q$1:$Q$1000<>"")*
    ('M:\999 Governance\Error Handling\[error handling issues data Jen new version.xlsx]Master sheet'!$Q$1:$Q$1000>1)*
    ('M:\999 Governance\Error Handling\[error handling issues data Jen new version.xlsx]Master sheet'!$Q$1:$Q$1000<=5))

    You should be able to apply this to your other ranges, i.e. 6 to 10, 11 to 40 etc.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

    Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ 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] Sumproduct v Count IFs (or both)
    By Matt1998 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-23-2015, 09:59 AM
  2. Count SUMPRODUCT Members / Average of SUMPRODUCT
    By Shingaru in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-10-2014, 03:59 PM
  3. Using a SumProduct Count to find a SumProduct Total?
    By XL021710 in forum Excel General
    Replies: 3
    Last Post: 02-18-2010, 08:31 AM
  4. sumproduct to count
    By profector in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-24-2008, 05:43 PM
  5. Count if and Sumproduct
    By Brento in forum Excel General
    Replies: 2
    Last Post: 06-20-2006, 04:05 PM
  6. [SOLVED] Sumproduct to count
    By GregR in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-05-2006, 04:10 PM
  7. Sumproduct to count
    By GregR in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-23-2005, 02:05 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