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

    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
    Excel Version
    You can use this array formula for the totals


Posting Permissions

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