count exact matches across a range

[Seattlites]

I have a spreadsheet where I have used a conditional formatting formula to highlight all the exact matches between multiple records and their individual values (rows across the spreadsheet) and a single record at the top. Every time there's an exact match, I highlight the cells a color. I would like to count all the cells that have a color (or use another function) to count all the exact matches between each of the individual items and their counterparts in the record at the top. There are hundreds of individual individual values/items to compare. Here's an example:

ITEM 1 ITEM 2 ITEM 3

RECORD TO COMPARE - STANDARD A 10 X
RECORD 1 A 15
RECORD 2 B 10
RECORD 3 A X

Based on this, the count for RECORD 1 would be 1, the count for RECORD 2 would also be 1 and the count for RECORD 3 would be 2 based on the number of individual matches.

I could copy all the conditional formatting as regular formatting and then replace all with a unique value and get a count that way. Anything easier?

[protonLeah]

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

[Seattlites]

thanks Ben. I know how to attach documents. I'm just curious if there's even a formulaic approach at all. I thought my example in the post was fairly straightforward, no? Probably easiest for me to convert all colored cells to static with a macro and then convert those to a unique value and count that value.

[MrShorty]

What formula or criteria are you using for the conditional formatting? If you are using a formula, it seems like it should be a very similar formula -- maybe in a COUNTIF() or COUNTIFS() function.

Based on the three records you posted and the results you suggest, something as simple as (assuming record 1 is in row 4) =COUNTIFS(B$4:B4,B4) would count how many times the "A" in B4 occurs in the range between B4 and Bcurrentrow (note how I used a mix of relative and absolute referencing to specify that kind of running count).

Is that the kind of thing you are looking for?

[Seattlites]

Thanks MrShorty. The values are all different though. What I would do if there were not 900 different columns evaluating exact matches using a formula within conditional foramatting is simply use COUNTIF or COUNTIFS because I would only be dealing with a handful of variables, but I actually need to evaluate if B4 = B1, H4 = H1, etc. all the way across the sheet and then count those that are true. Inserting individual columns for exact matches means I would insert hundreds of columns. I think perhaps converting the colors to static colors, replacing all of them with "unique value" and then counting those might be easiest. I would just transfer that back to my original sheet and then have counts...sounds kind of clunky, yet I know it would work.

[FlameRetired]

Seattlites when you say

to count all the exact matches

then A<>a, x<>X ... etc. I've taken that literally. Is that the meaning you intend?

I am not clear on exactly what you want to do on the conditional formatting part.

For the counts part here are three formulas ... array entered ... that count all the exact matches. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

	A	B	C	D	E	F
1				A	X	10
2	RECORD 1	A	15	1	1	1
3	RECORD 2	B	10	1	1	1
4	RECORD 3	A	X	2	2	2