SUMPRODUCT to count cells with background color

Joined
Jun 10, 2012
Messages
2
Reaction score
0
Hello Everyone,

I am trying to develop a monthly report in Excel 2003 (solution has to work in 2003), I have a sheet named "Purchase"
Columns M is with forecast date (format 01-Mar-12) but when cell background is filled with color (color can be different based of buyer choice) which means order actual date is same as forecast.

Following giving me total forecast orders for each month if I change month. I have a drop down at F4 from 2011 to 2020.

=IF('Purchase'!M2:M6000,SUMPRODUCT((EXACT(YEAR('Purchase'!M2:M6000),F4)*(EXACT(MONTH('Purchase'!M2:M6000),7)*('Purchase'!AC2:AC6000="UT")))),0)

However, I am hard time finding actual orders for those months. I have a

I have a function that I got from Internet that gives me total of none white cells or unfilled cells with color. The problem is how to use following criteria with above to find out actual orders for each month
=SUMPRODUCT(--(ColorIndex(COMMON!M2:M6000)<>2))

Any help in this regards will be greatly appreciated or to improve what I am written above.

NOTE: Again solution has to work in 2003.
 
Joined
Mar 20, 2012
Messages
764
Reaction score
4
I'm not sure of the ColorIndex function you're trying to use, but to count cells with a background color in Excel requires VBA. I'm not very strong with VBA but you can try this old post to see if it helps you.
 
Joined
Jun 10, 2012
Messages
2
Reaction score
0
Thanks Alow for your reply. My problem is not just count the cell with background colors.

The cell with background color are filled by conditional formatting (CF). I can easily count cell with filled color but the ColorIndex function does not count cells if conditional formatting is applied.

I am still struggling for that and probably this weekend will spend some time on VBA to change ColorIndex function to make sure it does what I am trying to achieve.
 
Joined
Mar 20, 2012
Messages
764
Reaction score
4
What you could potentially do is a COUNTIF or COUNTIFS (if you have Excel 2007, SUM array, or SUMPRODUCT if you have 2003) using criteria similar to your condition for your conditional formatting.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top