Find average based on if cell is included in mapping tab

Ethan Altmayer 0
Viewed 29

I'm looking to find the following Average, or even a Sum, based on if the value located in the column would return a TRUE in a vlookup.

enter image description here

So in the above example, I'm looking to find the average and sum for apple and orange, based on me looking it up based on the word test.

excel-formula excel
2 Answers

For average try-

=AVERAGE(FILTER(B2:B4,MMULT(--(D2:G4="Test"),SEQUENCE(COLUMNS(D2:G4)))))

For sum try-

=SUM(FILTER(B2:B4,MMULT(--(D2:G4="Test"),SEQUENCE(COLUMNS(D2:G4)))))

enter image description here

Harun24hr0

For Larger Data you may need this.

Formula Lookup

WIL0
Related