Can anyone help me figure out how I can tweak my formula to return the sum of quantity for Apple in table 1 to table 2? Right now I am getting {various} because of the multiple values for Person column in Table 1. Here's my current formula = Lookup(Type,Table 1,Type,Quantity) returning the values for Table 2. I need Quantity in for Apple in Table 2 to be the SUM, i.e. 47.

Update: Ordinarily, a SumIf() within the same table should get me the right answer, but I am yet to figure out how to use the SumIf() within my lookup.

Thanks in advance.

I resolved this issue by creating a SumIf column in Table 1. Then I did my lookup on that column in Table 2. Thanks!

Hi,

Instead of using a lookup:

=Lookup(Type,Table 1,Type,Quantity) 

you can also use the following formula (but only on numeric values):

=Table 1:Quantity[Type=Type]

This will sum the values.

Regards,

Robert

Here is an interesting article to check out regarding various. 

@Jenny Franklin