Compare Two Columns and Count if or sum the number of times adjacent cells are the same or differnce

[dnelley]

Hello There,

I've searched the forum to get assistance on this issue but I don't think any post directly applies to my question.

I am not trying to specify a string in the formula, as although the sample below would indicate that there are only 12 possible variables that can be entered into the cells my actual data is more dynamic. I would like to compare columns and count the number of times adjacent cell are either the same or different.

Kindly assist. Below is my sample data set and expected results.
Looking forward to your replies.

SAMPLE DATA
A1 – January || B1 – March || C1 – March
A2 – January || B2 – January || C2 – March
A3 – March || B3 – April || C3 – April
A4 – January || B4 – January || C4 – January
A5 – April || B5 – April || C5 – May
A6 – December || B6 – December || C6 – December
A7 – February || B7 – March || C7 – March
A8 – March || B8 – March || C8 – March
A9 – March || B9 – March || C9 – March

Compare column A to column B:
Count the number of occurrences in which adjacent cells are the same
Answer should be 6
Count the number of occurrences in which adjacent cells a different
Answer should be 3

Compare column B to column C:
Count the number of occurrences in which adjacent cells are the same
Answer should be 7
Count the number of occurrences in which adjacent cells is different
Answer should be 2

 

[irakrakow]

In 4 columns (I, J, K, and L), I entered the following:

I1: =IF(A1=B1,1,0)
J1: =IF(A1=B1,0,1)
K1: =IF(B1=C1,1,0)
L1: =IF(B1=C1,0,1)

Then I copied the formula in each column to Row 9. In Row 10 I added the sum of the counts.

Column I has a 1 if A=B.
Column J has a 1 if A <> B
Column K has a 1 if B=C
Column L has a 1 if B <> C

I think there's either an array formula or some sort of COUNTIF that does it better, but I'm new to these functions. I would welcome a better way.

I guess you could hide columns I, J, K, and L so the user doesn't see it.

 

[dnelley]

Thank You for your feedback @irakrakow.
I'm really hoping for something much simpler than that. Like a single all-encompassing formula.

My actual data fills up a large amount of the spreadsheet already.
On my own I tried to work out a countif statement but haven't been successful.

Hopefully someone out there has some more insight.

Thanks again.

In 4 columns (I, J, K, and L), I entered the following:

I1: =IF(A1=B1,1,0)
J1: =IF(A1=B1,0,1)
K1: =IF(B1=C1,1,0)
L1: =IF(B1=C1,0,1)

Then I copied the formula in each column to Row 9. In Row 10 I added the sum of the counts.

Column I has a 1 if A=B.
Column J has a 1 if A <> B
Column K has a 1 if B=C
Column L has a 1 if B <> C

I think there's either an array formula or some sort of COUNTIF that does it better, but I'm new to these functions. I would welcome a better way.

I guess you could hide columns I, J, K, and L so the user doesn't see it.

 

[Marcelo Branco]

Count A=B
=SUMPRODUCT(--(A1:A9=B1:B9))
result: 6

Count A<>B
=SUMPRODUCT(--(A1:A9<>B1:B9))
result: 3

Same idea to compare columns B and C

Hope this helps

M.

 

[irakrakow]

Thank you, Marcelo. A MUCH better solution.

 

[lray801]

Count A=B
=SUMPRODUCT(--(A1:A9=B1:B9))
result: 6

Count A<>B
=SUMPRODUCT(--(A1:A9<>B1:B9))
result: 3

Same idea to compare columns B and C

Hope this helps

M.

Greetings,

First, thank you for this answer; it helped me quite a bit! I'm curious if you can help me take it up one more level: I have my data in a filter and I'd like for the count to be dynamic based on the results that are currently showing in the filter. Does that make sense?

Thanks!

 

[Marcelo Branco]

Greetings,

First, thank you for this answer; it helped me quite a bit! I'm curious if you can help me take it up one more level: I have my data in a filter and I'd like for the count to be dynamic based on the results that are currently showing in the filter. Does that make sense?

Thanks!

Please post a data sample and expected result(s)

M.

 

[lray801]

Essentially what I have is column I (current) and K (destination) with city and states. Sometimes I filter that data to only look at particular cities and states. The count currently looks at if the two columns do, or do not, contain the same data. This way I can see if they're where they're destined or still en route.
Example:
I | K
Stockton, CA | Stockton, CA
Stockton, CA | Stockton, CA
Thedalles, OR | Thedalles, OR
Wamsutter, WY | Wamsutter, WY
Flanigan, NV | Willcox, AZ
Flanigan, NV | Willcox, AZ

Currently, my formula will tell me which rows have matching cells:
=SUMPRODUCT(--(I2:I273=K2:K273))=4
Or how many don't match:
=SUMPRODUCT(--(I2:I273<>K2:K273))=2

Now if I filter the data to remove any cells/rows with Flanigan, NV, the results stay the same because it's looking at the entire range regardless of what's actually being displayed by the filter. Ideally, the results would change to 4/0, respectively.

Does that help?

Thanks!

 

[Marcelo Branco]

See if this example helps

Before filter


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]

Field1​

[/td][td]

Field2​

[/td][/tr]

[tr][td]

2​

[/td][td]

Stockton, CA​

[/td][td]

Stockton, CA​

[/td][/tr]

[tr][td]

3​

[/td][td]

Stockton, CA​

[/td][td]

Stockton, CA​

[/td][/tr]

[tr][td]

4​

[/td][td]

Thedalles, OR​

[/td][td]

Thedalles, OR​

[/td][/tr]

[tr][td]

5​

[/td][td]

Wamsutter, WY​

[/td][td]

Wamsutter, WY​

[/td][/tr]

[tr][td]

6​

[/td][td]

Flanigan, NV​

[/td][td]

Willcox, AZ​

[/td][/tr]

[tr][td]

7​

[/td][td]

Flanigan, NV​

[/td][td]

Willcox, AZ​

[/td][/tr]

[tr][td]

8​

[/td][td] [/td][td] [/td][/tr]

[tr][td]

9​

[/td][td] [/td][td] [/td][/tr]

[tr][td]

10​

[/td][td]

Count A=B​

[/td][td] [/td][/tr]

[tr][td]

11​

[/td][td]

4​

[/td][td] [/td][/tr]

[tr][td]

12​

[/td][td] [/td][td] [/td][/tr]
[/table]

Formula in A11
=SUMPRODUCT(--(A2:A7=B2:B7),--(SUBTOTAL(3,OFFSET(A2:A7,ROW(A2:A7)-ROW(A2),0,1))))

Apply filter (uncheck in Field1, for example, Stockton, CA)


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]

Field1​

[/td][td]

Field2​

[/td][/tr]
[/tr]
[/tr]

[tr][td]

4​

[/td][td]

Thedalles, OR​

[/td][td]

Thedalles, OR​

[/td][/tr]

[tr][td]

5​

[/td][td]

Wamsutter, WY​

[/td][td]

Wamsutter, WY​

[/td][/tr]

[tr][td]

6​

[/td][td]

Flanigan, NV​

[/td][td]

Willcox, AZ​

[/td][/tr]

[tr][td]

7​

[/td][td]

Flanigan, NV​

[/td][td]

Willcox, AZ​

[/td][/tr]

[tr][td]

8​

[/td][td] [/td][td] [/td][/tr]

[tr][td]

9​

[/td][td] [/td][td] [/td][/tr]

[tr][td]

10​

[/td][td]

Count A=B​

[/td][td] [/td][/tr]

[tr][td]

11​

[/td][td]

2​

[/td][td] [/td][/tr]

[tr][td]

12​

[/td][td] [/td][td] [/td][/tr]
[/table]

Observe the result in A11

M.