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!
Bookmarks