Count number of rows in multiple arrays with identical values

Hi,
I have an excel sheet with a set number of IDs. For each ID, I am receiving data from three sources. The data from each source is stored in separate columns. For each ID number, I can receive data from either 1, 2, 3, or no data sources.

I need to be able to count the number of times the data for a particular ID number, from any sources, is identical. For example, for ID 3 I could receive data with the value 5 from all data sources. For ID 10 I could receive data with the value of 9 from source 1 and the 7 from source 2. In that case, I would count ID 1, but not ID 2.

I have attached a spreadsheet with a simplified example of the datastructure I have. I have a suspicion I should be able to do this with a SUMPRODUCT or an array function, but am not sure how to do this.

Any help appreciated

Steven

Originally Posted by Steven Fleck

Hi,
I have an excel sheet with a set number of IDs. For each ID, I am receiving data from three sources. The data from each source is stored in separate columns. For each ID number, I can receive data from either 1, 2, 3, or no data sources.

I need to be able to count the number of times the data for a particular ID number, from any sources, is identical. For example, for ID 3 I could receive data with the value 5 from all data sources. For ID 10 I could receive data with the value of 9 from source 1 and the 7 from source 2. In that case, I would count ID 1, but not ID 2.

I have attached a spreadsheet with a simplified example of the datastructure I have. I have a suspicion I should be able to do this with a SUMPRODUCT or an array function, but am not sure how to do this.

Any help appreciated

Steven

Are you just trying to establish which IDs contain the same numbers as suggested by the *s in column F, and what do you mean by 'counted'
Could you expand on this a little, giving specific examples of the results you expect and how you calculate them.

Rgds

Many thanks for the reply.

Yes - what I need is simply the count of the number of IDs that contain identical values in two or more of the data columns. This is indeed a count of the *s in column F in my example sheet.

Specifically, ID 1 has a value of 6 from three data sources, ID 5 has a value of 2 from two data sources, ID 6 has a value of 3 from two data sources, and ID 9 has a value of 5 from three data sources. This would give me a count of 4.

Obviously, in the simplified example I could for example create some simple formula to check on each row if the values in the three data source columns are identical. However, I have thousands of rows, so I would like to avoid having a formula on each row.

I tried SUMPRODUCT, and if I only try to count identical values in the first two data columns, this formula would work:

SUMPRODUCT(($B$2:$B$11=$C$2:$C$11)*1)

However, I cannot figure out how to extend this formula to check for identical values across three or more rows.

Hope this helps - do let me know if more clarification is needed!

Many thanks