# Counting Conditional Formating instances in multiple ranges

#### justme

##### Well-known Member
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

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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 { }

I'll give it a try.

Thanks.

Try this, worked for me;

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

Replies
1
Views
180
Replies
13
Views
323
Replies
5
Views
117
Replies
3
Views
240
Replies
1
Views
327

1,212,134
Messages
6,106,141
Members
447,997
Latest member
excelLover711

### 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.

### Which adblocker are you using?

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

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