Sum a column if two criteria are met
 

I need a formula that sums column C for each "Loc" by "Item"?

A B C
LOC Item WA Txn Value
1241 R3331 595.20
1241 R3334 595.20
1241 R3334 1,488.00
1242 R1400 908.46
1242 R1400 908.46
1242 R3334 1,488.00
1242 R3334 1,190.40
1243 R1400 908.46
1243 R3334 297.60


--
swestberry

Sum a column if two criteria are met
 

Hi,

Try this

=SUMPRODUCT((A2:A20=1241)*(B2:B20="R3334")*(C2:C20 ))

You could use cell ref's for the lookup values instead of having them
embedded in the formula

=SUMPRODUCT((A2:A20=D1)*(B2:B20=D2)*(C2:C20))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"swestberry" wrote:

I need a formula that sums column C for each "Loc" by "Item"?

A B C
LOC Item WA Txn Value
1241 R3331 595.20
1241 R3334 595.20
1241 R3334 1,488.00
1242 R1400 908.46
1242 R1400 908.46
1242 R3334 1,488.00
1242 R3334 1,190.40
1243 R1400 908.46
1243 R3334 297.60


--
swestberry

Sum a column if two criteria are met
 

This worked fine in I gave and should work on my spreadsheet. The data I am
looking up on is located on a different work sheet within same file. I am
using the sheet and cell references in formula. Would that make a
difference? I am getting a -0- result. I have the Loc # and Item # formated
as general.


--
swest


"Mike H" wrote:

Hi,

Try this

=SUMPRODUCT((A2:A20=1241)*(B2:B20="R3334")*(C2:C20 ))

You could use cell ref's for the lookup values instead of having them
embedded in the formula

=SUMPRODUCT((A2:A20=D1)*(B2:B20=D2)*(C2:C20))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"swestberry" wrote:

I need a formula that sums column C for each "Loc" by "Item"?

A B C
LOC Item WA Txn Value
1241 R3331 595.20
1241 R3334 595.20
1241 R3334 1,488.00
1242 R1400 908.46
1242 R1400 908.46
1242 R3334 1,488.00
1242 R3334 1,190.40
1243 R1400 908.46
1243 R3334 297.60


--
swestberry

Sum a column if two criteria are met
 

Thank you!!!!!!!!!!!!! I got it to work beautifully.
swest


"Mike H" wrote:

Hi,

Try this

=SUMPRODUCT((A2:A20=1241)*(B2:B20="R3334")*(C2:C20 ))

You could use cell ref's for the lookup values instead of having them
embedded in the formula

=SUMPRODUCT((A2:A20=D1)*(B2:B20=D2)*(C2:C20))
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"swestberry" wrote:

I need a formula that sums column C for each "Loc" by "Item"?

A B C
LOC Item WA Txn Value
1241 R3331 595.20
1241 R3334 595.20
1241 R3334 1,488.00
1242 R1400 908.46
1242 R1400 908.46
1242 R3334 1,488.00
1242 R3334 1,190.40
1243 R1400 908.46
1243 R3334 297.60


--
swestberry