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

LakeWaconia
LakeWaconia ✭✭✭✭
edited 01/03/24 in Formulas and Functions

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

  • Brian_Richardson
    Brian_Richardson Overachievers Alumni

    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.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!