SUMPRODUCT to count cells with background color

[SHAB]

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.

 

[alow]

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.

 

[SHAB]

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.

 

[alow]

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.