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