SUM columns based on 2 pieces of logic

[LOGICAL]

(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".

 

[Jim Thomlinson]

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

 

[Bob Phillips]

=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)