Counting Conditional Formating instances in multiple ranges

justme

Well-known Member
Joined
Aug 26, 2002
Messages
729
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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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 { }
 
Upvote 0
Try this, worked for me;

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

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

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
Back
Top