# I need assistance with an Excel Lookup Function

Advertisement

I need assistance with an Excel Lookup Function

Just making sure I have it right. If all [SubPartNo] for a particular [PartNo] have a [Purchased] value of 1, you want the [Import] column to have a "Y" for all rows of that particular [PartNo].This is pretty straightforward. You're going to want to nest versions of the SUM and COUNT functions inside of an IF formula.If you have your table formatted as a table in Excel 2010 or higher:Text=IF ( SUMIFS ( [Purchased],[PartNo],[@PartNo] ) = COUNTIFS ( [PartNo],[@PartNo] ), "Y", "N" )If not, based on the visible rows in your attached image (you can adjust the 14 to be the actual last row of your data):

Text=IF ( SUMIFS ( $C$2:$C$14, $A$2:$A$14, $A2 ) = COUNTIFS ( $A$2:$A$14, $A2 ), "Y", "N" )What these are doing is summing the [Purchased] column for all rows where the [PartNo] is the same as the current row. Then it is comparing that to the...

Repaly

I have the following table where I need to determine which parts I can import into our new

database. The criteria is that all of the values in the purchased Column for each PartNo must = 1 in order to mark a Y in the Import field. Can anyone help me solve this? I've got several thousand to sort through.

This topic first appeared in the Spiceworks Community

database. The criteria is that all of the values in the purchased Column for each PartNo must = 1 in order to mark a Y in the Import field. Can anyone help me solve this? I've got several thousand to sort through.

This topic first appeared in the Spiceworks Community

**Read More:**The other 3 answers