search and sum

[Guest]

Stocks were distributed on different days which I named as "monday",
"tuesday"..."sunday". The list has stock code, price, qty and amount. I want
sum of quantity in B1 and amount in C1, when I enter stock code in A1.
Oh my god somebody help me.

 

[Pete_UK]

You can use SUMIF( ) to do this. If you enter stock code in A1 then in
C1 enter the formula:

=SUMIF(A2:A2000,A1,C1:C2000)

(from your description I think quantity will be in column C, but you
can put this in B1 instead if you want to). Similarly, if amount is in
column D, enter this is D1:

=SUMIF(A2:A2000,A1,D12000)

I've assumed the data extends to row 2000 - adjust if necessary. Not
sure what you want to do with the days information in your posting.

Hope this helps.

Pete

 

[Guest]

Thanks Mr Pete. So you want me to repeat this formula 7 times in qty cell B!
& amount cell D1. Is there any chance, I give all the array names (monday,
tuesday,..) in formula, like looking in monday if the code is there get the
sum, likewise in tuesday.. that is giving all array names look for code &
their qty / amount. Sumproduct() will be useful ?. Bcos it will be easy for
me to skip any day or keep only 3days like, to reconcile dispensing account.
thanks a million.