Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum multiple vlookup returns across multiple columns
Hi All,
I need to sum the results of all occurences of 'Brand A' across 4 categories; $, QTY, $YA, QTY YA table would look like this: A B C D E Brand $ Qty $YA QtyYA Brand1 10 2 20 4 Brand2 45 45 10 12 Brand1 20 4 10 4 I need a formula in a cell that sums Brand1 by $ (should=30), A seperate cell should read by Qty (should=6) etc, etc, I was certain that I had done this before with sumproduct, but using the same formula isn't working. Also I seem to remember something about entering the formula with a 'ctrl,shift,enter' Please help me out. Submitted via EggHeadCafe - Software Developer Portal of Choice Generic Feed Parsers Redux http://www.eggheadcafe.com/tutorials...sers-redu.aspx |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum multiple vlookup returns across multiple columns
Try this...
This data in the range A1:E4 A B C D E Brand $ Qty $YA QtyYA Brand1 10 2 20 4 Brand2 45 45 10 12 Brand1 20 4 10 4 Summary section: B10:E10 = column headers $, Qty, $YA, QtyYA A11 = some brand name like Brand1 Enter this formula in B11: =SUMIF($A$2:$A$4,$A11,INDEX($B$2:$E$4,,MATCH(B$10, $B$1:$E$1,0))) Copy across to E11 then down as needed. -- Biff Microsoft Excel MVP <not_so_pro wrote in message ... Hi All, I need to sum the results of all occurences of 'Brand A' across 4 categories; $, QTY, $YA, QTY YA table would look like this: A B C D E Brand $ Qty $YA QtyYA Brand1 10 2 20 4 Brand2 45 45 10 12 Brand1 20 4 10 4 I need a formula in a cell that sums Brand1 by $ (should=30), A seperate cell should read by Qty (should=6) etc, etc, I was certain that I had done this before with sumproduct, but using the same formula isn't working. Also I seem to remember something about entering the formula with a 'ctrl,shift,enter' Please help me out. Submitted via EggHeadCafe - Software Developer Portal of Choice Generic Feed Parsers Redux http://www.eggheadcafe.com/tutorials...sers-redu.aspx |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum multiple vlookup returns across multiple columns
Try
=SUMPRODUCT(--(A1:A10="Brand1"),(B1:B10)) adjust your range according to the no of rows you have... You can also use =SUMPRODUCT(--(A1:A10=H1),(B1:B10)) if H1 contains the value Brand1 "not_so_pro" wrote: Hi All, I need to sum the results of all occurences of 'Brand A' across 4 categories; $, QTY, $YA, QTY YA table would look like this: A B C D E Brand $ Qty $YA QtyYA Brand1 10 2 20 4 Brand2 45 45 10 12 Brand1 20 4 10 4 I need a formula in a cell that sums Brand1 by $ (should=30), A seperate cell should read by Qty (should=6) etc, etc, I was certain that I had done this before with sumproduct, but using the same formula isn't working. Also I seem to remember something about entering the formula with a 'ctrl,shift,enter' Please help me out. Submitted via EggHeadCafe - Software Developer Portal of Choice Generic Feed Parsers Redux http://www.eggheadcafe.com/tutorials...sers-redu.aspx . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple returns from vlookup | Excel Worksheet Functions | |||
add multiple returns using vlookup | Excel Worksheet Functions | |||
VLOOKUP - MULTIPLE RETURNS | Excel Worksheet Functions | |||
Vlookup with multiple returns | Excel Worksheet Functions | |||
Vlookup Multiple Returns #REF | Excel Discussion (Misc queries) |