CODEWITHSUNDEEP
excelexcel-formula
chif-ii
chif-ii

Reputation: 1015

How do I sum all entries that match a lookup table?

So I have a column of numbers I want to sum. Each number is associated with a value on a lookup table. I need to find what values I want to use to sum based on the lookup table itself.

enter image description here

Basically, I need to sum everything in column I based on whether the entry in column J matches a MFG in column M that matches a reference number in column O. I have tried multiple iterations of SumProduct and array formulas, and have had no luck getting it to work. Everything I've read online about summing based on multiple criteria only works if you know ahead of time how many conditions you have to match; I don't have that information.

Upvotes: 0

Views: 573

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

This will iterated through the second table and if the Ref Num match the input in M16 then it returns the SUMIF() value of MFG from the first table and sums them all together:

=SUMPRODUCT((M16=$O$7:$O$12)*SUMIF(J:J,$M$7:$M$12,I:I))

Notice that the references to Table 2 are limited to the actual data, while the references to table 1 can be full column references. That has to do with how the formula will iterate. As it literally will iterate through the second table we want to limit the number of iterations to the data set itself. While the SUMIF() formula is already optimized and has no detriment to full column references.

enter image description here

Upvotes: 1

Related Questions