|
Sumif() with criteria
Today I had need to bring into my Sheet2 some data off of Sheet1;
My Sheet1 data (Column and Row#'s provided) G H J K 12 450300 Widget1 1,234.00 Y 13 500200 Widget2 2,111.00 Y 14 450300 Widget3 3,111.00 N 15 650200 Widget4 4,111.00 Y 15 450300 Widget5 5,111.00 Y 15 353700 Widget6 6,111.00 Y 16 450300 Widget7 2,333.00 N On mY Sheet 2 I need to Bring bank the sum of ColJ of all records where ColG = 450300 and ColK = Y the answer would be 6,345.00 What would formula be? Can/Should I use a: Sumif() Sumproduct() An Array-entered formula TIA, |
One way
In Sheet2 Assuming A2 contains: 450300 you could put in B2: =SUMPRODUCT((Sheet1!$G$1:$G$100=A2)*(Sheet1!$K$1:$ K$100="Y"),Sheet1!$J$1:$J$ 100) B2 can be copied down for other values in A3, A4 .. Adapt the ranges to suit .. (but you can't use entire col refs in SUMPRODUCT) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Jim May" wrote in message news:nZaRd.42327$EG1.27480@lakeread04... Today I had need to bring into my Sheet2 some data off of Sheet1; My Sheet1 data (Column and Row#'s provided) G H J K 12 450300 Widget1 1,234.00 Y 13 500200 Widget2 2,111.00 Y 14 450300 Widget3 3,111.00 N 15 650200 Widget4 4,111.00 Y 15 450300 Widget5 5,111.00 Y 15 353700 Widget6 6,111.00 Y 16 450300 Widget7 2,333.00 N On mY Sheet 2 I need to Bring bank the sum of ColJ of all records where ColG = 450300 and ColK = Y the answer would be 6,345.00 What would formula be? Can/Should I use a: Sumif() Sumproduct() An Array-entered formula TIA, |
Max, thanks..
(but you can't use entire col refs in SUMPRODUCT) by this do you mean that on Sheet2 if I have 50 records I must use the Sumproduct() in a helper column in each of the 50 rows VERSUS entering it once in a single cell like a true array-entered formula would do? Could/Would an array-entered formula do? TIA, Jim "Max" wrote in message ... One way In Sheet2 Assuming A2 contains: 450300 you could put in B2: =SUMPRODUCT((Sheet1!$G$1:$G$100=A2)*(Sheet1!$K$1:$ K$100="Y"),Sheet1!$J$1:$J$ 100) B2 can be copied down for other values in A3, A4 .. Adapt the ranges to suit .. (but you can't use entire col refs in SUMPRODUCT) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Jim May" wrote in message news:nZaRd.42327$EG1.27480@lakeread04... Today I had need to bring into my Sheet2 some data off of Sheet1; My Sheet1 data (Column and Row#'s provided) G H J K 12 450300 Widget1 1,234.00 Y 13 500200 Widget2 2,111.00 Y 14 450300 Widget3 3,111.00 N 15 650200 Widget4 4,111.00 Y 15 450300 Widget5 5,111.00 Y 15 353700 Widget6 6,111.00 Y 16 450300 Widget7 2,333.00 N On mY Sheet 2 I need to Bring bank the sum of ColJ of all records where ColG = 450300 and ColK = Y the answer would be 6,345.00 What would formula be? Can/Should I use a: Sumif() Sumproduct() An Array-entered formula TIA, |
Got it -- never mind
{=SUM((MyDirects!$D$7:$D$82=TestFormula!C10)*(MyDi rects!$M$7:$M$82="Y")*MyDi rects!$L$7:$L$82)} "Max" wrote in message ... One way In Sheet2 Assuming A2 contains: 450300 you could put in B2: =SUMPRODUCT((Sheet1!$G$1:$G$100=A2)*(Sheet1!$K$1:$ K$100="Y"),Sheet1!$J$1:$J$ 100) B2 can be copied down for other values in A3, A4 .. Adapt the ranges to suit .. (but you can't use entire col refs in SUMPRODUCT) -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "Jim May" wrote in message news:nZaRd.42327$EG1.27480@lakeread04... Today I had need to bring into my Sheet2 some data off of Sheet1; My Sheet1 data (Column and Row#'s provided) G H J K 12 450300 Widget1 1,234.00 Y 13 500200 Widget2 2,111.00 Y 14 450300 Widget3 3,111.00 N 15 650200 Widget4 4,111.00 Y 15 450300 Widget5 5,111.00 Y 15 353700 Widget6 6,111.00 Y 16 450300 Widget7 2,333.00 N On mY Sheet 2 I need to Bring bank the sum of ColJ of all records where ColG = 450300 and ColK = Y the answer would be 6,345.00 What would formula be? Can/Should I use a: Sumif() Sumproduct() An Array-entered formula TIA, |
(but you can't use entire col refs in SUMPRODUCT)
Entire col refs are for example: A:A, B:B, C:C which you can't use in SUMPRODUCT You need to use ranges such as: A1:A100, B1:B100, etc by this do you mean that on Sheet2 if I have 50 records I must use the Sumproduct() in a helper column in each of the 50 rows VERSUS entering it once in a single cell like a true array-entered formula would do? No, what was meant was that should you have other values listed in A3, A4, etc besides 450300 in A1, e.g: in A3: 500200, in A4: 650200 and you want the same criteria to be applied, then you could just copy B2 down to B4 to return the corresponding results in B3 and B4 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
| All times are GMT +1. The time now is 06:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com