|
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". |
SUM columns based on 2 pieces of logic
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". |
SUM columns based on 2 pieces of logic
=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" wrote in message ... (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". |
| All times are GMT +1. The time now is 11:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com