I have two worksheets. Worksheet one contains a data list and a column populated by a COUNTIF formula. The COUNTIF formula counts the total number of times a value occurs in the table in worksheet two.
WS1.JPG
Worksheet two has a table with data validation based on the data list in worksheet one.
WS2.JPG
What I need to do is for each value in the data list, count the number of times that value occurs twice on a single row and subtract that number from the number calculated by the COUNTIF formula. This new formula would populate a new column 'C' in worksheet one.
So in the above example for value 5088 there is one instance of it occuring twice in a single row (C2 & E2) so the new column C would show 3 (4-1). For 6039 there are 3 instances of it occuring twice in a single row (C4 & E4, C8 & E8, D10 & F10) so the new colum C would show 6 (9-3).
WS1-NEW.JPG
Can someone please help me with the formula to calculate this. Thanks in advance.
Bookmarks