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

1. ## 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.

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  Reply With Quote

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.  Reply With Quote

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.  Reply With Quote

4. 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)  Reply With Quote

#### Posting Permissions

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