Conditional formatting not highlighting all 'true' values

RaiJ

New Member
Joined
Apr 26, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi there,
I needed to highlight the same positive and negative figures in column S, if columns C and F were the same, aka:
Apple Orange -1
Apple Orange 1
Apple Banana 1
Apple orange 1
Apple orange -1

When I created the formula below to find true/false values on a separate column, it worked like a charm:
=SUMPRODUCT(--($C43&$F43&$S43=$C$2:C43&$F$2:F43&$S$2:S43))<=SUMPRODUCT(--($C43&$F43&-$S43=$C$2:$C$282&$F$2:$F$282&$S$2:$S$282))

But when I use/tyoe in same formula in conditioning formating, so it highlights 'true' values (i.e. positive and negative figures of the same kind:
Apple Orange -1
Apple Orange 1
it does highlight but not everything. I can cross-reference it with that extra column with 'true/false' values.
Ideally, I would not want to have any extra column, I want to make sure conditional formating works fine, but it is not, as it also highlighted a couple of 'false' values.
1619436084167.png
1619436128216.png
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,414
Office Version
  1. 365
Platform
  1. Windows
Difficult to diagnose from an image, but does the Applies to range start at S2?
 

RaiJ

New Member
Joined
Apr 26, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Yep, it was a bit messed up. It is sorted now, thank ou for looking :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,414
Office Version
  1. 365
Platform
  1. Windows
You're welcome.
You could also change your formula to
Excel Formula:
=COUNTIFS(C$2:C2,C2,F$2:F2,F2,S$2:S2,S2)<=COUNTIFS($C$2:$C$282,C2,$F$2:$F$282,F2,$S$2:$S$282,-S2)
which should be a bit more efficient.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,271
Messages
5,657,760
Members
418,411
Latest member
Excellency

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