Find a value in multiple sheets and compare it to the qty in the next column

LakeWaconia

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.

Brian_Richardson

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.