I have a worksheet similer to the following with data range names:

User Item Debit
Adam Lunch $1.70
Alex Milk $0.30
Alex Lunch $1.55
Adam Snack $0.50

This is the formula I can't get to compute:
=if(and(user="Adam",item="Lunch"),sum(Debit),""

Any clue as to why it will not work?

Close, just re-arrange some formulas. Also have to commit this with
CTRL+SHIFT+ENTER as it is an array formula:

=SUM(IF(((user="Adam")*(item="Lunch")),debit,""))


--
Regards,
Dave


"PCakes" wrote:

I have a worksheet similer to the following with data range names:

User Item Debit
Adam Lunch $1.70
Alex Milk $0.30
Alex Lunch $1.55
Adam Snack $0.50

This is the formula I can't get to compute:
=if(and(user="Adam",item="Lunch"),sum(Debit),""

Any clue as to why it will not work?

Because you want it to evaluate more than one row of data - try it as
an array formula

=sum(if(user="Adam",if(item="Lunch",Debit,0)))

entered as ctrl shift enter to make it an array formula

This works wonderfully, I am trying to understand Arrays. Thank you!

"David Billigmeier" wrote:

Close, just re-arrange some formulas. Also have to commit this with
CTRL+SHIFT+ENTER as it is an array formula:

=SUM(IF(((user="Adam")*(item="Lunch")),debit,""))


--
Regards,
Dave


"PCakes" wrote:

I have a worksheet similer to the following with data range names:

User Item Debit
Adam Lunch $1.70
Alex Milk $0.30
Alex Lunch $1.55
Adam Snack $0.50

This is the formula I can't get to compute:
=if(and(user="Adam",item="Lunch"),sum(Debit),""

Any clue as to why it will not work?

This also worked very well, thank you for your help.

" wrote:

Because you want it to evaluate more than one row of data - try it as
an array formula

=sum(if(user="Adam",if(item="Lunch",Debit,0)))

entered as ctrl shift enter to make it an array formula