 # Excel Calculation Needed

• Hi everyone,

Back after two months with a back injury/broken rib.

Can anyone give me a calculation that will enable me to to the following -

In M8:M50 I have a one word category i.e. Quarter, Half, Full or 8.

In N8:N50 I have a discount column (always 20%).

In 08:050 I have a cost.

I want to know the numer of times Quarter appears in the range with 20% discount and the total cost

Then I want to know the number of times Quarter appears in the range without 20% discount and the total cost.

Then same for the other three categories

So I end up with eight calculations.

i.e. if there were 20 entries and 15 of them had 20% discount and the cost per entry was £10 I would want the result to be 15 Quarters at a cost of £120, and the other calculation would be 5 Quarters at a cost of £50.

Hope that makes sense

Tim.

• Welcome back Tim.
The Sumproduct function is what you need. For the discounts use
=SUMPRODUCT((M8:M50="Quarter")*(N8:N50>0)*(1-N8:N50)*O8:O50)
and for the non-discount use
=SUMPRODUCT((M8:M50="Quarter")*(N8:N50=0)*O8:O50)

• Let D2:G2 be your values (Quarter, Half,Full,8)
Let C3 = 20%
Leave C4 empty

In D3 type the following formula

=SUMPRODUCT((\$M\$8:\$M\$50=D\$2)*(\$N\$8:\$N\$50=\$C3),\$O\$8:\$O\$50)

copy across to G3 & down to row 4

You should then have your 8 formulas, the results of which should add up to the SUM of O8:O50

On my test this works fine...

• Hope you are feeling better. This is probably not really what you meant, but if you truly want to return both the quantities and total values with just 8 formulas, you'll need to create a string something like: SUMPRODUCT((M8:M50="quarter")*(N8:N50>0)) & " discounted quarters totalling " & "£" & SUMPRODUCT((M8:M50="quarter")*(N8:N50>0)*(1-N8:N50)*(O8:O50))

• Thanks for all the guidance guys all of your calculations work if i apply them to manually keyed in figures, but I have one problem the figures in columns M,N, & O are all the result of IF calculations.

Can I build something into the SUMPRODUCT calculation to compensate for this?

I have been using Derk's calculation as it came through first.

Tim.

• Quote from Timbo

but I have one problem the figures in columns M,N, & O are all the result of IF calculations.

So what? It shouldn't make any difference

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!