PDA

View Full Version : Solved: Sum of certain data for every unique client



almouchie
01-15-2006, 09:29 AM
i have data in a sheet
& i want to sum each client's data ( specific cells) for in another sheet

I have clients names in cloumn C (each row has all transactions done)
I want to sum certain data for every client in a sheet
I want to add all }

i want to add column O. cloumn U, & column AI for every client in this sheet

what formula can I use
( i dont want to use pivot table)
any help would be appreciated

almouchie
01-15-2006, 10:50 AM
to make it clearer
in the main sheet is every transaction listed with all the clients names (column C)

I want to add for every client (suming all transactions), the total amount earned,due,borrowed etc each found in a column (for instance the data found in column O, column U)

Bob Phillips
01-15-2006, 12:11 PM
=SUMPRODUCT(--(Sheet1!A2:A200=A2),Sheet1!O2:O200)

will sum all amounts in column O on Sheet 1 for the client in A2 on Sheet2.

Likewise for other amounts.

almouchie
01-15-2006, 01:47 PM
thanks it worked out fine
i have a question thou why when i drag the formula down it changes the ranges & not only the cell A2
i dont want to manually fix every formula

mdmackillop
01-15-2006, 02:51 PM
Check your Excel Help for Relative and Absolute references.

almouchie
01-15-2006, 02:58 PM
will do
thanks mdmackillop

Bob Phillips
01-15-2006, 04:25 PM
thanks it worked out fine
i have a question thou why when i drag the formula down it changes the ranges & not only the cell A2
i dont want to manually fix every formula

Start with

=SUMPRODUCT(--(Sheet1!$A$2:$A$200=A2),Sheet1!$O$2:$O$200)

almouchie
01-16-2006, 01:26 AM
thanks a lot :)
that worked out great when i dragged the formula