counting colored cells

ndello

Active Member
Joined
Oct 16, 2002
Messages
382
Hi all,
I am using cells A15 through Z 100
I color code cells and want to add the totals. So in Cell A1 I want the total of all green colored cells. A2 I want the total of red, etc.
One caveat, I merge cells all throughout A15 to Z100, so I only want to count the colored cell once not each one within the merge.

Any chance on a formula for that?

Thanks so much
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,564
Office Version
  1. 365
Platform
  1. Windows
First piece of advice -- get rid of the merged cells! They will cause you nothing but grief!
They are one of the biggest headaches in Excel, and most serious users won't touch them with 10 foot pole.
You can achieve the same visual effect without all the issues by using the "Center Across Selections" format option, as explained here: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

Second, how exactly are your cells being colored?
If they are being colored by Conditional Formatting, you may be able to use the same conditions you are using for that in a SUMIF or SUMIFS formula (sum by the same conditions you are doing the Conditional Formatting on).

If they are being colored manually, you will need to use VBA, as Excel functions only run off of cell values, not cell formatting.
Here is a link that shows you how to do that: Sum by color using VBA in Microsoft Excel
 
Solution

ndello

Active Member
Joined
Oct 16, 2002
Messages
382
Thanks and silly question, where do i find center across selection, I just searched the whole ribbon (no doubt I missed it)
I cannot use conditional,
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,564
Office Version
  1. 365
Platform
  1. Windows
Thanks and silly question, where do i find center across selection
Select the range.
Right-click on it.
Select "Format Cells"
Go to "Alignment" tab.
It is under the "Horizontal" drop-down box.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,348
Messages
5,624,145
Members
416,014
Latest member
MickP69

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
Top