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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

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()))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,020
Messages
5,834,989
Members
430,331
Latest member
Syed Yasir Hannan

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
Top