Prev: Summing across columns while skipping some columns
Next: How do I count instances of a reference?
From: not_so_pro on 26 Feb 2010 11:03
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/aspnet/42a9b6e2-809e-4ca7-b3f6-acd41f462063/generic-feed-parsers-redu.aspx
From: T. Valko on 26 Feb 2010 12:18
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 news:201022611257jason.prowse(a)me.com...
> 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/aspnet/42a9b6e2-809e-4ca7-b3f6-acd41f462063/generic-feed-parsers-redu.aspx


From: Sheeloo on 26 Feb 2010 12:58
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/aspnet/42a9b6e2-809e-4ca7-b3f6-acd41f462063/generic-feed-parsers-redu.aspx
> .
>
 | 
Pages: 1
Prev: Summing across columns while skipping some columns
Next: How do I count instances of a reference?