software

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=quot;Adamquot;,item=quot;Lunchquot;),sum(Debit),quot;quot;

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=quot;Adamquot;)*(item=quot;Lunchquot;)),debit,quot;quot;))--
Regards,
Davequot;PCakesquot; wrote:

gt; I have a worksheet similer to the following with data range names:
gt;
gt; User Item Debit
gt; Adam Lunch $1.70
gt; Alex Milk $0.30
gt; Alex Lunch $1.55
gt; Adam Snack $0.50
gt;
gt; This is the formula I can't get to compute:
gt; =if(and(user=quot;Adamquot;,item=quot;Lunchquot;),sum(Debit),quot;quot;
gt;
gt; 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=quot;Adamquot;,if(item=quot;Lunchquot;,Debit,0)))

entered as ctrl shift enter to make it an array formulaThis works wonderfully, I am trying to understand Arrays. Thank you!

quot;David Billigmeierquot; wrote:

gt; Close, just re-arrange some formulas. Also have to commit this with
gt; CTRL SHIFT ENTER as it is an array formula:
gt;
gt; =SUM(IF(((user=quot;Adamquot;)*(item=quot;Lunchquot;)),debit,quot;quot;))
gt;
gt;
gt; --
gt; Regards,
gt; Dave
gt;
gt;
gt; quot;PCakesquot; wrote:
gt;
gt; gt; I have a worksheet similer to the following with data range names:
gt; gt;
gt; gt; User Item Debit
gt; gt; Adam Lunch $1.70
gt; gt; Alex Milk $0.30
gt; gt; Alex Lunch $1.55
gt; gt; Adam Snack $0.50
gt; gt;
gt; gt; This is the formula I can't get to compute:
gt; gt; =if(and(user=quot;Adamquot;,item=quot;Lunchquot;),sum(Debit),quot;quot;
gt; gt;
gt; gt; Any clue as to why it will not work?

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

quot; wrote:

gt; Because you want it to evaluate more than one row of data - try it as
gt; an array formula
gt;
gt; =sum(if(user=quot;Adamquot;,if(item=quot;Lunchquot;,Debit,0)))
gt;
gt; entered as ctrl shift enter to make it an array formula
gt;
gt;