Count cells with specific values in row with matching ID across sheets

Herwart

Hi,

I have the following requirement:

• I have 2 sheets, each with the [Project Number] column as the ID. Let´s call the sheets [Intake Sheet] and [Picking List]
• The [Intake Sheet] holds, for example, a range of 10 columns in which machine part names are entered. Let´s call the columns [Machine part 1] … [Machine part 10] and the machine part names "Bolt", "Nut", "Fitting", …
• The [Picking List] has columns for each machine part.
• Now I want to count in the [Picking List] based on the [Project Number] how often the corresponding machine part occurs in the matching row of the [Intake Sheet].

Example: In the [Intake Sheet] the "Bolt" is named in the row with matching project number in columns [Machine part 1], [Machine part 4], [Machine part 5] and [Machine part 10] – i.e. 4 times. I want to have the result "4" in the column [Bolt] of the sheet [Picking List].

I have already tried to combine COUNTIF and INDEX MATCH, but failed. What must the column formula for the [Bolt] column in the [Picking List] sheet look like? 

Many thanks in advance ☺️

Paul Newcome

You will need to create multiple COUNTIFS (one for each part column) and then add them together.

=COUNTIFS({Project ID}, @cell = [Project ID]@row, {Part 1}, @cell = [Picking List]@row) + COUNTIFS({Project ID}, @cell = [Project ID]@row, {Part 2}, @cell = [Picking List]@row) + COUNTIFS({Project ID}, @cell = [Project ID]@row, {Part 3}, @cell = [Picking List]@row) + ...................................

Paul Newcome

You will need to create multiple COUNTIFS (one for each part column) and then add them together.

=COUNTIFS({Project ID}, @cell = [Project ID]@row, {Part 1}, @cell = [Picking List]@row) + COUNTIFS({Project ID}, @cell = [Project ID]@row, {Part 2}, @cell = [Picking List]@row) + COUNTIFS({Project ID}, @cell = [Project ID]@row, {Part 3}, @cell = [Picking List]@row) + ...................................