+ Reply to Thread
Results 1 to 20 of 20

COUNTIFS with SUMPRODUCT Not working; Simple Formula Correction please!

  1. #1
    Forum Contributor her.rockstar's Avatar
    Join Date
    01-11-2013
    Location
    Saint John, NB
    MS-Off Ver
    Office 365
    Posts
    194

    COUNTIFS with SUMPRODUCT Not working; Simple Formula Correction please!

    Hello, I'm trying to get better at using proper and succinct formulas.

    Case in point, I have a formula that has multiple criteria from one column, using countifs. I'd like to simplify it with SUMPRODUCT but it's not working right.

    Here's the formula I have (correctly returns 79)
    =COUNTIFS(Table13[Action Overall Severity],"Orange",Table13[Current Action Status],"<>Cancelled",Table13[Current Action Status],"<>Complete",Table13[Current Action Status],"<>")

    Here's the formula I'm trying (returns 837)
    =SUMPRODUCT(COUNTIFS(Table13[Action Overall Severity],"Orange",Table13[Current Action Status],"<>"&{"Cancelled","Complete",""}))

    Can somebody please help me straighten this out?
    Last edited by her.rockstar; 10-29-2019 at 11:03 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: Simple Formula Correction please!

    Rule 01: Thread Titles

    Hi. We'd like to help... but.

    Your thread title is VERY weak. It should resemble the terms you would use if you were searching Google for an answer. Your title would produce a squillion useless hits.

    So... Please take a moment to amend your thread title. Make sure that the title properly explains your request. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    NO HELP to be offered until a sensible title has been provided.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: Simple Formula Correction please!

    Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please DO NOT attach a picture of an Excel sheet (I do not have the patience to re-type any/all your stuff before starting).

    1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

    2. Make sure that your sample data are truly REPRESENTATIVE of your real data. For example, don't show text in a column if it's really a number. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    3. Make sure that your desired solution is also shown (mock up the results manually). To be honest, I am not interested in seeing a non-working formula... or a pile of blank cells. However, I am very interested in seeing your EXPECTED results in their EXPECTED location.

    4. Try not to use merged cells. They cause lots of problems and are DEFINITELY best avoided!

    Unfortunately the attachment icon doesn't work at the moment. So,... instead click on Go Advanced (below the Edit Window) while composing your reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

  4. #4
    Forum Contributor her.rockstar's Avatar
    Join Date
    01-11-2013
    Location
    Saint John, NB
    MS-Off Ver
    Office 365
    Posts
    194

    Re: Simple Formula Correction please!

    I've updated the title to the post. Hopefully it's a little more clear.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: COUNTIFS with SUMPRODUCT Not working; Simple Formula Correction please!

    Thanks. We just need the workbook now, please.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  6. #6
    Forum Contributor her.rockstar's Avatar
    Join Date
    01-11-2013
    Location
    Saint John, NB
    MS-Off Ver
    Office 365
    Posts
    194

    Re: COUNTIFS with SUMPRODUCT Not working; Simple Formula Correction please!

    I'm afraid I haven't got a workbook to give. I thought a syntax question would be easy to answer. Sorry about that.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: COUNTIFS with SUMPRODUCT Not working; Simple Formula Correction please!

    No workbook? How can that be? We do not need to see sensitive data, just a representative sample so that we can help you sort this out.

    You have marked this thread as solved, but as far as I can see, it isn't yet.

  8. #8
    Forum Contributor her.rockstar's Avatar
    Join Date
    01-11-2013
    Location
    Saint John, NB
    MS-Off Ver
    Office 365
    Posts
    194

    Re: COUNTIFS with SUMPRODUCT Not working; Simple Formula Correction please!

    It's solved. I was hoping to have a discussion about the way the formula is built. Not just to find a solution. For example "typically, based on just looking at your sumproduct formula, you wouldn't use the countifs function", etc. Clearly nobody is open to discussing how a formula is built.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: COUNTIFS with SUMPRODUCT Not working; Simple Formula Correction please!

    You didn't ask for a discussion. You said this:

    Case in point, I have a formula that has multiple criteria from one column, using countifs. I'd like to simplify it with SUMPRODUCT but it's not working right.
    Based on this, we asked to see a workbook. If you just want to discuss the issue, let's start with a couple of questions: why do you feel that using SUMPRODUCT would simplify your formula? Which bit of the COUNTIFS formula do you consider too complex or clumsy?

  10. #10
    Forum Contributor her.rockstar's Avatar
    Join Date
    01-11-2013
    Location
    Saint John, NB
    MS-Off Ver
    Office 365
    Posts
    194

    Re: COUNTIFS with SUMPRODUCT Not working; Simple Formula Correction please!

    Thank you for responding with some dialogue. I would argue by the way that I said
    Hello, I'm trying to get better at using proper and succinct formulas.
    which would indicate I'm looking to learn.

    As far as I understand, saying COUNTIF + COUNTIF + COUNTIF for the same column is wasted space, whereas SUMPRODUCT{"a","b","c"} is the succinct, cleaner, and generally more accepted way of writing the formula. I was hoping to have my assumptions validated.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: COUNTIFS with SUMPRODUCT Not working; Simple Formula Correction please!

    Whilst it is true you can use this positively, I don not believe you are going to get it to work negatively:

    Table13[Current Action Status],"<>"&{"Cancelled","Complete",""}

    This is where your idea is failing.

    Seeing the issue in context would give us all a more holistic view of your problem, and by showing it in context, there might be ideas we could offer to help you simplify this, but not necessarily in the way you envisage. By not sharing the context, we are straight-jacketed by your own preconceptions. That's all.

  12. #12
    Forum Contributor her.rockstar's Avatar
    Join Date
    01-11-2013
    Location
    Saint John, NB
    MS-Off Ver
    Office 365
    Posts
    194

    Re: COUNTIFS with SUMPRODUCT Not working; Simple Formula Correction please!

    Thank you for your help. You have solved the problem, and also provided me with the knowledge I needed.

    I swapped the formula to count the positive instances:
    =SUMPRODUCT(COUNTIFS(Table13[Action Overall Severity],"Orange",Table13[Current Action Status],{"In Progress","Reassigned","*undefined*"}))

    and the result is 79.

    I'm sorry for the way this came about, but I am appreciative of your help.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: COUNTIFS with SUMPRODUCT Not working; Simple Formula Correction please!

    No problem. You got rather close to throwing out the baby with the bathwater, but we managed to save it.

    I would advise you to mention 'discussion' in thread titles if that's the tack you wish to take, otherwise we tend to assume that it's a problem that requires solving.

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: COUNTIFS with SUMPRODUCT Not working; Simple Formula Correction please!

    I was looking at altrernatives, but I don't think there are any. Other than the obvious:

    =SUMPRODUCT(($A$2:$A$11="Orange")*($B$2:$B$11<>"Cancelled")*($B$2:$B$11<>{"Complete"}))

    seee sheet.
    Attached Files Attached Files

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: COUNTIFS with SUMPRODUCT Not working; Simple Formula Correction please!

    Idiot me. There is a way:

    =SUMPRODUCT(--($A$2:$A$11="Orange")*(ISNA(MATCH($B$2:$B$11,{"Cancelled","Complete"},0))))
    Attached Files Attached Files

  16. #16
    Forum Contributor her.rockstar's Avatar
    Join Date
    01-11-2013
    Location
    Saint John, NB
    MS-Off Ver
    Office 365
    Posts
    194

    Re: COUNTIFS with SUMPRODUCT Not working; Simple Formula Correction please!

    Thank you for posting this - does the "--" allow sumproduct to work with the negative search?

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,401

    Re: COUNTIFS with SUMPRODUCT Not working; Simple Formula Correction please!

    No. The -- allows Excel to convert text values into number values that can then be used in a SUMPRODUCT matrix.

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: COUNTIFS with SUMPRODUCT Not working; Simple Formula Correction please!

    1. No. It converts TRUE to 1 and FALSE to zero.

    2. On this occasion, it's not needed. Duhh!!!

    =SUMPRODUCT(($A$2:$A$11="Orange")*(ISNA(MATCH($B$2:$B$11,{"Cancelled","Complete"},0))))
    works equally well.

  19. #19
    Forum Contributor her.rockstar's Avatar
    Join Date
    01-11-2013
    Location
    Saint John, NB
    MS-Off Ver
    Office 365
    Posts
    194

    Re: COUNTIFS with SUMPRODUCT Not working; Simple Formula Correction please!

    I see. Thanks for your help. Thanks to both of you.

  20. #20
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: COUNTIFS with SUMPRODUCT Not working; Simple Formula Correction please!

    However, if you're into that sort of thing... COUNTIFS (on my sample sheet) is the shortest formula.

+ 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] IF and INT formula correction
    By mazan2010 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-24-2016, 04:26 AM
  2. Simple MSGBOX code correction
    By Nisha_NishaDelhi in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-18-2014, 04:51 PM
  3. [SOLVED] Formula correction
    By Kym-B in forum Excel General
    Replies: 2
    Last Post: 01-09-2014, 07:27 AM
  4. [SOLVED] IF formula correction
    By mra1984 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-06-2013, 12:36 PM
  5. correction of formula please............
    By azmi in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-11-2005, 04:05 PM
  6. [SOLVED] Re: Formula correction HELP!!!
    By Jay in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-02-2005, 04:06 PM

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