Sum column entries by color

[slc813]

Hi. I have a column with invoicing amounts which have different background cell colors. I need to be able to sum the data of each color. I've tried the "How to count and sum cells based on background color in excel" search but it doesn't seem to work. Can someone help me out?

Or is there a conditional formatting answer?

Thanks!

[mehmetcik]

How are the cells colured? Manually or by conditional formatting?

[kev_]

welcome to the forum

A copy of your workbook would help us avoid having to recreate anything and to understand your data

Are your cells coloured using conditional formatting or fixed formatting?
What are the rules determining choice of colours?

Plesae attach a copy of your workbook.
Ensure data is made anonymous and
- that it is clear what you are after
- even if it means manually mocking up the desired results.

thanks

ManageAttachments3.jpg

[leelnich]

Hi slc813, welcome to the forum! There is NO WORKSHEET FORMULA that can identify the background color of a cell. You CAN sort and filter by color and use the SUBTOTAL function to get your sums, but if you have lots of colors, that would be tedious. VBA- either as a User-Defined Function (UDF) or a macro called on demand- would seem to be your best bet. We'll need a sample workbook...

[slc813]

Conditionally. I'm trying to upload example.

[slc813]

Capture.JPG

Thanks!

[kev_]

You can use the same formula (adapted) to sum the cells as used to conditionally format them.
What's the formula?

[kev_]

Try this
- it makes use of the letters in column E

with formula in H2 copied down:
=SUMIF(E:E,G2,D:D)

where :
- D:D is the range containing values to be summed
- G2 contains A, N or U
- E:E is range containing A,N,U attributable to each value in D:D
- The 2 ranges must be the same size

SumByColour.jpg