Why is SUBTOTAL including SUM?

**lincolnelectra** · 03-15-2017, 10:32 PM

Hi,
Really sorry if this is a dumb question or if it's a topic that's been covered - I have searched and googled and nothing has answered my question.
I made a spreadsheet to track how much I'm spending on product - I have a bunch of cells with dollar amounts, then the sum of them, then a discount is applied, taxes, S&H, and then I SUBTOTAL to get the total of the order. I'm trying to pull the total of each order into another cell on another sheet, so I can have a grand total of how much I've invested. My problem is, SUBTOTAL is including the SUMs, so the total is way bigger than it should be. I thought the purpose of SUBTOTAL was to only total the data that used that function? Am I do something wrong? Or is the a different function I can use somewhere?
I know I could manually enter the grand total cell as cell+cell, but that is more manual entry than I'd like to do.
I've attached the spreadsheet for reference.

Thanks in advance!

Bianca

**sandy666** · 03-15-2017, 10:39 PM

Read here SUBTOTAL() and here SUM()

**lincolnelectra** · 03-15-2017, 10:46 PM

I did, and did again, but that does not solve my problem. Is that your subtle way of telling me there's no easy way to achieve what I'm looking for?

**sandy666** · 03-15-2017, 10:52 PM

No, it was answer for your post title

What you want to sum and subtotal (without big nvovels serialized

)

btw. there is SUBTOTAL with function no. 9 so it works like normal SUM() function.
or you want hide rows or something like that.
I don't see the need for a SUBTOTAL()

**FDibbins** · 03-15-2017, 11:14 PM

SUBTOTAL will exclude other SUBTOTAL functions within it's range, but not other regular functions

**FDibbins** · 03-15-2017, 11:17 PM

huh OK, I loaded your file, and that is not supposed to be happening. the ST is not ignoring other ST's in it's range. I will see if I can find whats wrong

**sandy666** · 03-15-2017, 11:20 PM

You can try this one on Summary E1: =SUMIF(Orders!E:E,"Total",Orders!F:F) or if you want sum total from subtotals change Total to Subtotal inside function.
It will give you correct answer on Summary if you did all SUMs correct on Orders tab.
And on Orders tab use standard SUM() with titles Subtotal or Total there, where they are necessary. (or Ford solution)

**FDibbins** · 03-15-2017, 11:22 PM

OK found the problem

For each SUBTOTAL you have there (F19, F32 etc) you are using a regular SUM function. Change those to ST and you should be good to go

Why is SUBTOTAL including SUM?

LinkBack

Thread Tools

Rate This Thread

Display

Why is SUBTOTAL including SUM?

Re: Why is SUBTOTAL including SUM?

Re: Why is SUBTOTAL including SUM?

Re: Why is SUBTOTAL including SUM?

Re: Why is SUBTOTAL including SUM?

Re: Why is SUBTOTAL including SUM?

Re: Why is SUBTOTAL including SUM?

Re: Why is SUBTOTAL including SUM?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Subtotal including first Row of next group

SUBTOTAL ... used in complex if-then results in assumption of SUBTOTAL usage on True logic

Subtotal not including the row 2

Subtotal range above blank rows, subtotal in the last row adjacent right column

Subtotal - Delete zero Subtotal and prior rows that calculate to that zero Subtotal

subtotal dialog box including column header

Copy worksheet including named ranges not including macros

Subtotal including a Count and Sum

Bookmarks

Bookmarks

Posting Permissions