Solved: Sum of certain data for every unique client

[almouchie]

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]

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]

=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]

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]

Check your Excel Help for Relative and Absolute references.

[almouchie]

will do
thanks mdmackillop

[Bob Phillips]

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]

thanks a lot
that worked out great when i dragged the formula