Sumif() with criteria

[Jim May]

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]

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)

 

[Jim May]

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

 

[Jim May]

Got it -- never mind

{=SUM((MyDirects!$D$7:$D$82=TestFormula!C10)*(MyDirects!$M$7:$M$82="Y")*MyDi
rects!$L$7:$L$82)}

 

[Max]

(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