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!

Register Yourself Login