Count cells with specific values in row with matching ID across sheets
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 ☺️
Best Answer
-
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) + ...................................
Answers
-
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) + ...................................
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 220 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!