Count cells that are equal with condition

This is what I'm trying to automate:

Capture.JPG

I have a list of data in Table1.
In column G I want to calculate the number of instances Joe, Bill and Marta have the same figures in Table1[ColumnB] and in Table1[ColumnC]. I.e. the red cells for each name.

I've come up with ={SUM(IF(Table1[ColumnB]<>Table1[ColumnC];1;0))}, which gives me the grand total of equal cells, but I want to break it down to each name as well.



I could insert a calculation in ColumnD with

=IF([@[ColumnB]]<>[@[ColumnC]];1;0)

...which would add 1 when ColumnB and ColumnC are equal

... and in Column G sum it up with

=SUMIFS(Table1[ColumnD];Table1[ColumnA];$F2) note: (F2 = Joe)

... but I want to do it all in one calculation without adding columns

Help!

Originally Posted by ottoex

Both works, thanks guys!

I've never used =sumproduct before and I'm trying to figure out how it works, as it seems to be a "neater" solution than using nested ifs (no disrespect pwnyadav007!)

Why do you use multiplication teethless mama?

The multiplication turns TRUE/FALSE into 1/0. The SUMPRODUCT is use to add them all up.

See this for more info on SUMPRODUCT:

http://xldynamic.com/source/xld.SUMPRODUCT.html