SUMPRODUCT with fill color criteria

zuluz

New Member
Joined
Aug 7, 2017
Messages
2
I want to SUMPRODUCT 2 equal ranges in 2 columns but only choose those cells that have a particular fill color in 1 of those columns. Possible?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
There are no functions to work with colors.
But you can make your own functions (User Define Function).
With de Visual Basic Editor can you create a UDF.

Code:
Function ColorCode(rng As Range)
    ColorCode = rng.Interior.ColorIndex
End Function

In your sheet you can fill a extra column with '=ColorCode(A2)'.
 
Last edited:
Upvote 0
Welcome to the MrExcel board!

Here is another possible work-around.
In the table below, we want a SUMPRODUCT of columns A & D but only where column A is green.

To demonstrate, I have a standard SUMPRODUCT in G1 which calculates the value for all rows in the range. The formula in H1 gives the same result.

Excel Workbook
ABCDEFGH
1Hdr 1Hdr 2Hdr 3Hdr 4Hdr 5149149
287838
387836
4477210
5798710
672323
736819
8310693
SUMPRODUCT Colour



Now set up AutoFilter on the table, or at least column A (if it isn't already) and use 'Filter by Color' on column A for the green colour.

The result is below, and cell H1 gives the result that you are looking for.

Excel Workbook
ABCDEFGH
1Hdr 1Hdr 2Hdr 3Hdr 4Hdr 514965
387836
672323
8310693
SUMPRODUCT Colour
 
Upvote 0
Thank you both for your response.. i got another function that takes as input a cell/range along with the "color of interest" and returns true/false.. i used that inline in my SUMPRODUCT call and works like charm..
 
Upvote 0
Thank you both for your response.. i got another function that takes as input a cell/range along with the "color of interest" and returns true/false.. i used that inline in my SUMPRODUCT call and works like charm..
Just out of curiosity, as long as you have to call out to a VBA function anyway, why are you having it do only half the job... why not let it pull the values in for those cells it assesses as TRUE and sum them and return that sum instead of having it return TRUE/FALSE values for an Excel SUMPRODUCT to add up for you?
 
Last edited:
Upvote 0
Thank you both for your response.. i got another function that takes as input a cell/range along with the "color of interest" and returns true/false.. i used that inline in my SUMPRODUCT call and works like charm..

I hope you are still on this board. I have been trying to do the same thing and have had no luck. Can you send me the function you built? Thanks.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top