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

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

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.

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.

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)