Find a value in multiple sheets and compare it to the qty in the next column
I'm trying to create a formula in the column [Purchased Parts Available?] that uses the [Job #]@row field in, looks for it in 15 other sheets (Purchased Parts 1.....Purchased Parts 15) and sees if the Qty Available in the those sheets is greater than 0. If all locations found with the [Job #]@row value have a Qty greater than 0, then return a value of "1", else deliver a "0". I've tried COUNTIF(S) with VLOOKUP, but can't use VLOOKUP because it only returns the first value found. See example below.
Answers
-
I think you can accomplish this with a series of simple COUNTIF statements added together for one column, let's call it "Total Parts":
= COUNTIF( {Purchased Parts 1-Job #} , [Job #]@row ) + COUNTIF ( {Purchased Parts 2-Job #} , [Job #]@row ) + ...etc
Then use an IF formula in the [Purchased Parts Available?] column to see if that total count is greater than 0. I'm assuming that it's a checkbox column.
= IF ( [Total Parts]@row>0, true )
or = IF ([Total Parts]@row>0, "Yes" ) if it's a text column or picklist.
Note that you cannot mash together a COUNTIFS or SUMIFS statement that looks across multiple sheets, as those functions require that the range is the same size for each of the criteria. In other words, you can't do = COUNTIFS ({Sheet 1},Job@row, {Sheet 2},Job@row, etc) because the Sheet 1 and Sheet 2 ranges are different.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!