Sumif() with criteria

  • Thread starter Thread starter Jim May
  • Start date Start date
J

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,
 
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)
 
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
 
Got it -- never mind

{=SUM((MyDirects!$D$7:$D$82=TestFormula!C10)*(MyDirects!$M$7:$M$82="Y")*MyDi
rects!$L$7:$L$82)}
 
(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?
Click to expand...

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
 
Back
Top