Results 1 to 4 of 4

Thread: Trying to find a way to calculate subtotals for different sections data validation

  1. #1

    Trying to find a way to calculate subtotals for different sections data validation



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Seems like what I'm trying to do here goes beyond formulas but I'm not sure what I need.

    Like the title says,


    I'm creating an invoice form using data validation. The invoice will have different sections that need a total, and then a grand total at the bottom.


    You might want to download my example so you can see what I'm talking about.


    I have 2 data validation drop downs. It's pretty self explanatory if you look at my sheet you'll be able to see what I'm doing.


    What I need help with is I would like to be able to choose an option in the data validation menu that will show a total of the items above it. Then I will start a new section and I want to be able to do the same thing for each section as needed.


    At the end of the invoice I want to be able to choose Grand Total from the list and have it give me a total for everything.


    Does that make sense?


    Any help appreciated
    Attached Files Attached Files

  2. #2
    Update!


    I think I know what I'm trying to do now but I still don't know how to do it.


    Correct me if I'm wrong but I think I need an array formula


    Basically I need to set boundaries for the formula to work within.


    When I select "section" from the drop down in A4, I need that to create the beginning of the boundary in F4 & G4.


    When I select "Total" from the drop down in A11 I need that to set the end boundary for the formula that sums the values in columns F & G. I then need that to display the sum in F11 & G11.

  3. #3
    Let me try and explain this a little better.


    There is supposed to be a drop down where I have section and total but I haven't added it yet. Let's just pretend it's there.


    I'm creating an invoice worksheet. I may have a customer who needs an estimate for both their house and another property. I want to be able to give a sub total for each, and then a grand total for all.


    Lets pretend A4 is a drop down, I select "section", and then Main House. The other fields are left blank, but I want to use this row as a reference point for my running total to start adding up.


    Then, after I've entered all of my items for the Main House, I want to go to A11 and select total. I want it to then display the sum of F5:F10 in F:11 and G5:G10 in G11.


    Then I select a new section in A12, it starts a new reference point for the next total. The next total just adds F13:15 & G13:15


    Then in A17 I would select Grand Total and it would sum all of the values in columns F & G. I now realize I would need to move the output for the subtotals to different columns for that to work.


    I need it to work like this because each invoice will have a different amount of rows, and using the usual references won't work out for what I'm trying to do.

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,841
    Articles
    0
    Excel Version
    O365
    You can use this array formula for the totals

    =SUM(INDEX(F$1:F10,MAX(IF($A$1:$A10="Total",ROW($A$1:$A10),1))+1):F10)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •