Counting Conditional Formating instances in multiple ranges

justme

Well-known Member
Joined
Aug 26, 2002
Messages
722
Hope everyone had a great Thanksgiving!

I searched the board and it seems like every time someone wanted to count the number of times a color appeared on their worksheet because of conditional formatting they are told to count the number of times the condition is met.

So, I do that and I have two columns with this formula
=SUMPRODUCT(--(J7:J65< TODAY()))+SUMPRODUCT(--(J71:J84< TODAY()))

It is working great, the only thing is, is that sometimes the same row is highlighted in col. I and J. When this happens I would be double counting an occurance. How can I now reduce or count the number of times I have met the condition in both columns I & J, so I can subtract that from the total? ie: if column I & J meet the condition it should only be counted once.

I don't know why the formula didn't post - I'll try again

=SUMPRODUCT(--(I7:I65< TODAY()))+SUMPRODUCT(--(I71:I84< TODAY()))

FYI: I may name a few ranges to make it easier to capture the four secitons.

Edited by Von Pookie: fixed formula display
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

jimboy

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
Hi - I'm not an expert on SUMPRODUCT (but it's a great function!) - but you could use the array formula;

Code:
=SUM(IF($I$7:$I$65>=TODAY(),IF($J$7:$J$65<TODAY(),1,0),0))

It will sum the instances when the first range is >= today and the second range is <= today.

You need to press CONTROL-SHIFT Enter when entering the formula (this will put these signs around the formula { }
 

jimboy

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
Try this, worked for me;

Code:
=SUMPRODUCT(--(J7:J65< TODAY()),--(I7:I65>=TODAY()))
 

Watch MrExcel Video

Forum statistics

Threads
1,118,530
Messages
5,572,686
Members
412,481
Latest member
nhantam
Top