Excel Datalist functions / lookup summary

[marius.coleraine]

Dear Excel Users,

My problem is following. I have one table with 2 columns and 30 rows.
In the right column the user can choose in a drop down list 1 of 5
ledgers (Accounting) for each row. In the left column I have dollar
values.
Now, filling in the rows 1 to 30 the ledgers (right column) chosen will
repeat themselves.
At the bottom I would like to have 5 independent cells showing a the
sum for one of the five accounts.

Example:

ledger number 1

row 1 5$
row 5 10 $
row 15 10 $

The sum cell one should sum all dollar values for ledger number 1 which
has entries in row 1,5 and 15. So the formula needs to go into each of
these rows and selects the left one of the colums in order read out the
dollar values and total them which is here 25$.

Same for ledger number 2 to 5 in different sum cells.

Anyone any idea how this works.

 

[Guest]

Hi
SUMPRODUCT will do this - if I understand you correctly. If your ledge
column is B, and your values are in A, try this:
=SUMPRODUCT(--($B$2:$B$1000=1),--($A$2:$A$1000))
This will sum all rows in A where the row in B equals 1.
Hope this helps.
Andy.

 

[CLR]

Try

=SUMIF(A1:A30,1,B1:B30)
=SUMIF(A1:A30,2,B1:B30)
etc etc

Vaya con Dios,
Chuck, cABGx3