SUMIF and AND function

D

Daren

I have a set of data with three columns in order of Year, Amount, Paid by.
The year is 2004, 2005, and 2006, with one year value appearing in each row
of column A. Differnt amounts are listed in the Amount column, and Paid By
contains three different people. I am trying to get a sum of what just one
of those three people paid in 2004 over the entire year, and my goal is to
just return the sum of what tha person paid in 2004. I've tried to enter an
argument that says the evaluation criteria is 2004, the criteria range as the
Paid By, and the sum range as the Amount paid range. I've done this using a
SUMIF, but could only get a total amount paid by the three people and not
just the one person I'm interested in. Can you help?
 
T

T. Valko

Try this:

Column A1:10 = year
Column B1:B10 = amount paid
Column C1:C10 = Paid by

=SUMPRODUCT(--(A1:A10=2004),--(C1:C10="Joe"),B1:B10)

Or, use cells to hold the criteria:

E1 = 2004
F1 = Joe

=SUMPRODUCT(--(A1:A10=E1),--(C1:C10=F1),B1:B10)

Biff
 
D

Daren

That worked. Thanks!!!

T. Valko said:
Try this:

Column A1:10 = year
Column B1:B10 = amount paid
Column C1:C10 = Paid by

=SUMPRODUCT(--(A1:A10=2004),--(C1:C10="Joe"),B1:B10)

Or, use cells to hold the criteria:

E1 = 2004
F1 = Joe

=SUMPRODUCT(--(A1:A10=E1),--(C1:C10=F1),B1:B10)

Biff
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top