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
Read More: The other 3 answers