CODEWITHSUNDEEP
excel
Sudo
Sudo

Reputation: 651

Compare columns in MS Excel

Following is a sample excel spreadsheet. The requirement is to compare each column (say B) with column C and count the number of rows in which both B and C have numbers. For instance, both B and C have values in the first and second rows but only B has a value in the third row, so the output should be 2.

I tried to use if condition in the following manner:

if(and(isnumber(b2),isnumber(c2)),1,0)

but it works only for one row not all rows.

How do I achieve this result in MS Excel?

╔═══╦════════╦═══╦═══╦═══╦═══╦═══╗
║   ║   A    ║ B ║ C ║ D ║ E ║ F ║
╠═══╬════════╬═══╬═══╬═══╬═══╬═══╣
║ 1 ║        ║ 4 ║ 5 ║   ║ 6 ║ 5 ║
║ 2 ║        ║ 2 ║ 5 ║   ║ 2 ║   ║
║ 3 ║        ║ 1 ║   ║ 2 ║ 6 ║ 7 ║
║ 4 ║        ║   ║   ║   ║   ║   ║
║ 5 ║ Output ║ 2 ║   ║ 0 ║ 2 ║ 1 ║
╚═══╩════════╩═══╩═══╩═══╩═══╩═══╝

Upvotes: 1

Views: 234

Answers (2)

zx8754
zx8754

Reputation: 56149

If you need to compare all column combinations then you can use SUMPRODUCT:

=SUMPRODUCT(--(ISNUMBER(INDIRECT($A6&"$1:"&$A6&"$3"))),--(ISNUMBER(B$1:B$3)))

enter image description here

See this link for explanation of "Using SUMPRODUCT as an array formula".

Upvotes: 1

jmstoker
jmstoker

Reputation: 3475

Place this formula where ever you want to see the results

=COUNTIFS(B1:B4,"<>",C1:C4,"<>")

The "<>" only counts non-blank cells, but it will count #NAME? ,#DIV/0, and #REF

This can be avoided if zero is also not an acceptable value by adding two more criteria

=COUNTIFS(B1:B4,"<>",C1:C4,"<>",B1:B4,">0",C1:C4,">0"!)

Upvotes: 1

Related Questions