+ Reply to Thread
Results 1 to 14 of 14

include ALL colour attributes in criteria range (SUMPRODUCT)

  1. #1
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    include ALL colour attributes in criteria range (SUMPRODUCT)

    Hi all,

    I am trying to sum the products of certain ranges, but only for certain attributes. The formula is supposed to sum the products of price and units, but only for the colour e.g. "blue", or "green". Though sometimes, depending on what attribute is selected in my comboBox, it should sum the products of ALL colours. In non array formulas, a simple "*" works to include all of them, unfortunately it doesn't seem to work in an array. Is there a different way to write it?

    Please Login or Register  to view this content.
    works!

    Please Login or Register  to view this content.
    Does not work!

  2. #2
    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: include ALL colour attributes in criteria range (SUMPRODUCT)

    You cannot use cell or font colour as a criterion. It may be possible with VBA - would you like me to move your thread to that section?
    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.

  3. #3
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: include ALL colour attributes in criteria range (SUMPRODUCT)

    I am referring to an item colour not a cell colour!

    e.g.

    Item Colour Price Units
    1 Blue 32$ 110,000
    2 Green 12$ 33,000
    3 Grey 3$ 25,999
    4 Black 25$ 23,000
    5 Grey 18$ 12,500
    6 Grey 12$ 10,000
    7 Green 4$ 5,000

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: include ALL colour attributes in criteria range (SUMPRODUCT)

    If you want to sum all colors, then just dont include that as a criteria?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: include ALL colour attributes in criteria range (SUMPRODUCT)

    I should have been more specific...

    I have a ComboBox where I select the colour I want to look at. If I select "Blue" from the ComboBox, it takes that as a criteria, but if I select "ALL", it is supposed to consider all (!) colours. The actual formula would be:

    Please Login or Register  to view this content.
    In a different dashboard, I am only summing data, hence I am using SUMIFS which works perfectly fine with the "*"

    Please Login or Register  to view this content.

  6. #6
    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: include ALL colour attributes in criteria range (SUMPRODUCT)

    Quote Originally Posted by esbencito View Post
    I should have been more specific...
    Yes!

    Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: include ALL colour attributes in criteria range (SUMPRODUCT)

    Agree a sample WB will help, but maybe if you have a selection called "all" in teh DD, then construct he formula using IF's such that when it sees ALL, it excludes the color criteria

  8. #8
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: include ALL colour attributes in criteria range (SUMPRODUCT)

    See sample workbook attached...

    Nested IFs won't work in this case as I have several criteria (up to 10) and the formula would

    1. get too long
    2. become hard to maintain.

    That's why my previous solution with "*" for my SUMIFS formula was very convenient. Unfortunately it doesn't seem to work in arrays.
    Attached Files Attached Files

  9. #9
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: include ALL colour attributes in criteria range (SUMPRODUCT)

    I haven't looked at the workbook yet but perhaps
    SUMPRODUCT(Range_Price,((Range_Colour=Selected_Colour)+("All"=Selected_Colour)),Range_Units)
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  10. #10
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: include ALL colour attributes in criteria range (SUMPRODUCT)

    That seems to work!! But I don't fully understand why... Could you explain what it does? Just trying to understand the logic in order to apply it to my other formulas.

  11. #11
    Registered User
    Join Date
    10-28-2008
    Location
    Turkey
    Posts
    88

    Re: include ALL colour attributes in criteria range (SUMPRODUCT)

    Hi,

    Here is your file.

    I recomend you to use Table, Pivot Table and slicer also a bit macro for auto update.

    .
    Attached Files Attached Files

  12. #12
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: include ALL colour attributes in criteria range (SUMPRODUCT)

    The + operates as an OR condition. You have two arrays of True/False values- the first is whether the range matches the selected colour, the second whether the selected colour is "All" (presumably they could not both be true!). So if the selected colour is "All" the array will contain 1 for every row (False for the first array and True for the second, hence 0+1); if it's a specific colour, you have the same criteria as before but with 0 added to it since the second criteria array is all False. Does that make sense?

  13. #13
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: include ALL colour attributes in criteria range (SUMPRODUCT)

    Quote Originally Posted by yurttas View Post
    Hi,

    Here is your file.

    I recomend you to use Table, Pivot Table and slicer also a bit macro for auto update.

    .
    Since this is an extremely simplified version of my data set, pivots will not work!

  14. #14
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: include ALL colour attributes in criteria range (SUMPRODUCT)

    Quote Originally Posted by xlnitwit View Post
    The + operates as an OR condition. You have two arrays of True/False values- the first is whether the range matches the selected colour, the second whether the selected colour is "All" (presumably they could not both be true!). So if the selected colour is "All" the array will contain 1 for every row (False for the first array and True for the second, hence 0+1); if it's a specific colour, you have the same criteria as before but with 0 added to it since the second criteria array is all False. Does that make sense?
    well explained! Thanks!! Smart and smooth workaround to get the required results.

+ 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. Replies: 2
    Last Post: 01-20-2017, 04:27 PM
  2. how to count attributes across multiple criteria
    By noobiexcel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-27-2016, 02:20 PM
  3. [SOLVED] Sumproduct using range as criteria to compare another range
    By lukihnio in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-25-2016, 11:07 AM
  4. Replies: 3
    Last Post: 04-21-2015, 07:45 AM
  5. SUMPRODUCT Criteria - Do Not Include Value From Separate Column
    By freybe06 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-11-2014, 10:26 AM
  6. SUMPRODUCT Criteria to Not Include Values within Column
    By freybe06 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-11-2014, 10:24 AM

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