Vlookup Total

[treva26]

Is it possible to do this:

Create a function like a vlookup that will:

select all rows where
column A = value 1
AND
column B = value 2

return the TOTAL of ALL figures in column C for those selected.


Or more simply-
Lookup column A and return the total of column B for all the matches.

[NBVC]

You can use Sumproduct()

e.g

=Sumproduct(--(A1:A100=value1),--(B1:B100=value2),C1:C2)

[treva26]

Sorry I dont understand..

Here is my worksheet.

My data is in 3 columns: O P and Q

So in my table, C3 needs to show the total number of the product number (B) in that month (C) from the qty's (Q)

There is often more than 1 listing of each product in each month. They need to be totalled.

[Paul]

Hi treva,

Put this formula into cell C3 and then fill right for each month, and fill downward to the bottom of your product list (in column B). It should give you the right totals.

Please Login or Register  to view this content.

[plitfyg]

Hi,

Please see the attachement. I hope it will work for you.

Plitfyg

[treva26]

Perfect, thanks guys

[treva26]

OK here's another question.

I have a similar problem and so far I have but its not working right:

Please Login or Register  to view this content.

So it looks up col E to match R4 (right?)
also matches col C to match S4 (right?)
Then it is supposed to give me the average of those numbers in col M (there should be 7 matches)

[rylo]

Hi

Mind is slow this arvo so can't make SUMPRODUCT tweak that way. Have to revert to the old array entered

=AVERAGE(IF((E4:E800=R4)*(C4:C800=S4),M4:M800,""))


HTH

rylo