SUM columns based on 2 pieces of logic

(col 1) (col 2) (col 3)
PRICE REP_ID FY_PD
5072 17 801
1008 17 801
14656 4 801
14760 68 802
1344 68 802
7302 4 802
1899 4 802
33978 4 802
904 68 802
6500 68 802
5640 4 803
1380 4 803
76500 68 803
3900 68 803
50660 4 803
7302 4 803
61326 68 803
500 4 803
3744 4 803
153000 68 803
60120 85 804
1908 68 804
6275 68 804

I am trying to create a summary of the above data using 2 inputs. I need
help on the function or nested functions.

If cell A1 = 68 and cell B1 = 802 I want to return the sum from the data
above of all prices (column 1) to cell C1 where B1 and C1 are true.

I was trying SUMIF() but that is only good for 1piece of logic not 2. Also
SUMIF() would not refer to a cell but required hard code of "=68".

James_Thomlinson
Fri May 09 17:29:02 CDT 2008

You could use sumproduct something like this...

=SUMPRODUCT(--($B$2:$B$24=E1), --($C$2:$C$24=F1), A2:A24)

where 68 is in E1 and 802 is in F1
here is a link for more info...
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"LOGICAL" wrote:

> (col 1) (col 2) (col 3)
> PRICE REP_ID FY_PD
> 5072 17 801
> 1008 17 801
> 14656 4 801
> 14760 68 802
> 1344 68 802
> 7302 4 802
> 1899 4 802
> 33978 4 802
> 904 68 802
> 6500 68 802
> 5640 4 803
> 1380 4 803
> 76500 68 803
> 3900 68 803
> 50660 4 803
> 7302 4 803
> 61326 68 803
> 500 4 803
> 3744 4 803
> 153000 68 803
> 60120 85 804
> 1908 68 804
> 6275 68 804
>
> I am trying to create a summary of the above data using 2 inputs. I need
> help on the function or nested functions.
>
> If cell A1 = 68 and cell B1 = 802 I want to return the sum from the data
> above of all prices (column 1) to cell C1 where B1 and C1 are true.
>
> I was trying SUMIF() but that is only good for 1piece of logic not 2. Also
> SUMIF() would not refer to a cell but required hard code of "=68".
>

Bob
Fri May 09 17:32:00 CDT 2008

=SUMPRODUCT(--(rng1=A1),--(rng2=B1),rng3)

unlike SUMIF, SUMPRODUCT in Excel prior to 2007 won't take whole columns, it
has to be a defined range.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"LOGICAL" <LOGICAL@discussions.microsoft.com> wrote in message
news:8BDA2E65-69B5-4FEF-8927-BC8D3BBA867D@microsoft.com...
> (col 1) (col 2) (col 3)
> PRICE REP_ID FY_PD
> 5072 17 801
> 1008 17 801
> 14656 4 801
> 14760 68 802
> 1344 68 802
> 7302 4 802
> 1899 4 802
> 33978 4 802
> 904 68 802
> 6500 68 802
> 5640 4 803
> 1380 4 803
> 76500 68 803
> 3900 68 803
> 50660 4 803
> 7302 4 803
> 61326 68 803
> 500 4 803
> 3744 4 803
> 153000 68 803
> 60120 85 804
> 1908 68 804
> 6275 68 804
>
> I am trying to create a summary of the above data using 2 inputs. I need
> help on the function or nested functions.
>
> If cell A1 = 68 and cell B1 = 802 I want to return the sum from the data
> above of all prices (column 1) to cell C1 where B1 and C1 are true.
>
> I was trying SUMIF() but that is only good for 1piece of logic not 2.
> Also
> SUMIF() would not refer to a cell but required hard code of "=68".
>