Count cells with specific values in row with matching ID across sheets
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 ☺️
Help Article Resources
Check out the Formula Handbook template!