# How to create a formula for weighing and summing an undefined number of rows?

#### bergen

##### New Member
Hello,

I am looking for help to crack this one: I want to calculate the total discount to customers based on discounts on individual products. However the number of products vary from each customer. How can I do this fast and automatic in excel? (And I am stuck with a column in between the two useful columns)

Below I will show you a sample of what information I have:
 A B C D Product Name Discount % N/A Quantity Product A 47% X 10 Product B 46% X 4 Product C 59% X 224

<tbody>
</tbody>

I was thinking something like this: =(B3*D3)+(B4*D4)+(B5*D5)/(D3+D4+D5)/3
But clearly I am bad at math since this doesn't give me anything close to 50.5%. And even if it did this formula does not allow for adding or subtracting products.

Can anyone help?

#### njimack

##### Well-known Member
I calculate the discount to be 58.2% using the below:
=SUMPRODUCT(B3:B5,D3:D5)/SUM(D3:D5)

To handle varying number of rows, consider using dynamic ranges: Excel Names -- Excel Named Ranges

#### bergen

##### New Member
Thank you Neil, works like a peach!