CODEWITHSUNDEEP
excelvlookup
omegacore
omegacore

Reputation: 1712

Using the current SUMIF "row" in criteria

I am trying to do something like this: =SUMIF(H2:H55,VLOOKUP(insert placeholder for current row,Interfaces_to_Devices,5,FALSE)=A4,J2:J55)

IE I am trying to use the current row that I am evaluating to do a VLOOKUP of a value that needs to be equal to something I specify.

Thank you.

enter image description here In this screen shot I did not include the VLOOKUP()="A4". That is supposed to be in there.

Upvotes: 3

Views: 10154

Answers (2)

user2140261
user2140261

Reputation: 7993

=SUMIF(H2:H55,VLOOKUP(ROW(),Interfaces_to_Devices,5,FALSE)=A4,J2:J55)

Or

=SUMIF(H2:H55,INDEX(Interfaces_to_Devices,5,ROW())=A4,J2:J55)

If you simply REFERENCE the first value in column 5 of Interfaces_to_Devices, then drag down your formula you will simply reference each value going down the column without the need for a lookup, index, or match.

Notice in the picture below how i replace your formula with the cell reference to the 5th column in the table and in my first Row i Reference the first value in the column then as i drag the formula down it automatically references the equivalent cells in each row down the column.

enter image description here

Upvotes: 1

Andy G
Andy G

Reputation: 19367

ROW() will return the number of the current row.

But the first argument to VLOOKUP is a cell reference or value.

If you explain what value you are trying to use in the VLOOKUP, how it is related to the current cell, then it is probably possible to use INDEX and MATCH (perhaps OFFSET) to achieve what you need.

Upvotes: 1

Related Questions