Lookup Help

[mdkinsey]

I have a workbook that I am tracking deposits for multiple stores. I am using the following formula:=LOOKUP(A4,Debit!$C$3:$C$32,Debit!$K$3:$K$32) where A4 is the account number and c3 - c32 are the account numbers and k3 - k32 are the deposit amounts. The formula is doing what I want except when there is no deposits for a store what happens then it will put the deposit amount of the last store in the cell rather than leave it blank. I would like to put a zero in the cell if there is no deposit. I am using 2003 excel. Any help would be appreciated.

 

[Avatar]

Try:

=SUM((Debit!$C$3:$C$32=$A$4)*(Debit!$K$3:$K$32))

It's an array formula so instead of enter, use Ctl+Shift+Enter

 

[mdkinsey]

I did that but it gives me the same answer for all cells. I have 26 different stores I am looking up.

 

[Dryver14]

try

=SUMIF(Debit!$C$3:$C$32,A4,Debit!$K$3:$K$32)

 

[mdkinsey]

Thanks much that worked. What if I wanted to add deposits from 2 different sheets?

 

[Avatar]

Trying to figure out why my original formula didn't work for you since Dryver14's does exactly the same thing.. well anyway a quickly modified version of mine would be:

=SUM((Debit!$C$3:$C$32=$A$4)*(Debit!$K$3:$K$32))+SUM(('Debit (2)'!$C$3:$C$32=$A$4)*('Debit (2)'!$K$3:$K$32))
But if the original didn't work, this one won't either..

so a quickly modified version of Dryver14's (without much throught as it's 00:44 here..) would be:

=SUMIF(Debit!$C$3:$C$32,A4,Debit!$K$3:$K$32)+SUMIF('Debit (2)'!$C$3:$C$32,A4,'Debit (2)'!$K$3:$K$32)

 

[mdkinsey]

Fantastic, works great, thank you very much