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

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

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
.