Sum of a set of values

**akki2907** · 01-23-2019, 07:54 PM

Hi All,

I am new to this forum and also Excel too.
I have hundreds of accounts and for each account there is a set of products.
I enter the count of products for each account manually every month.
And there is another field called 'Total deals' which is not the sum of the count of products.
I need to calculate this:

Sum(count of products)/total deals per account.

Sample.xlsx

Any help would be great.

Thanks,
Dpu

**benishiryo** · 01-23-2019, 08:35 PM

welcome to the forum, Dpu. try:
=SUMIF(Table2[Account],"C1",Table2[Count])/VLOOKUP("c1",Table2,4,0)

or place C1 somewhere in a cell such as O1.
=SUMIF(Table2[Account],O1,Table2[Count])/VLOOKUP(O1,Table2,4,0)

**akki2907** · 01-24-2019, 11:52 AM

Thank you benishiryo.

"=SUMIF(Table2[Account],"C1",Table2[Count])/VLOOKUP("c1",Table2,4,0)", this gives only the sum for account C1 right.

I need to calculate for all the accounts.
I have 80 accounts.

Any more suggestions please.

Thanks,
Dpu.

**Bo_Ry** · 01-24-2019, 12:48 PM

Please try at
F2
=IFERROR(INDEX(Table2[Account],MATCH(0,INDEX(COUNTIF(F$1:F1,Table2[Account]),),)),"")

E2 same
=IFERROR(SUMIFS(Table2[Count],Table2[Account],F2)/VLOOKUP(F2,Table2,4,),"")