Saturday, December 30, 2017

yogi_A Row By Row Single Formula For Line Item Costs And SubTotals To Be Dragged Down For Copying

Google Spreadsheet   Post  #2336

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Dec-31-2017


Hi there & Happy New Year to ya! :)

Currently, I'm Filtering in only the line items (from another price reference sheet) which have a Qty filled in.
HOWEVER, as I edit these quantities, the resulting invoice ranges grow and shrink as items are Filtered in and out (both the # of rows, AND the # of groups that need 'SUBTOTALING' ), the range size changes so...

I'm trying to EITHER 1) Automatically resize the ranges (preferably the starting cell) of the SUMIF  so that it's always just below (or ON, if necessary) a row where COLUMN A is blank AND so that it's the FIRST blank (closest) above the SUMIF (that I've added text "SUB= $" to appear to be a Subtotal).

OR 2) SUM ALL numbers in the cells that say "Sub= $" (really it's a SumIF) so that I can SUBTRACT all previous 'Sub= $' values from the current 'Sub= $' cell. That way they will LOOK LIKE SUBTOTAL fxns without needing subtotal in a different column.

AND while 3) looking to keep the invoice looking lean & clear, by keeping all Balance Amounts & Subtotals in the same column.

The two main issues I've run into are 
A) By putting the text, "Sub= $" into each Subtotaled item, I've made SUMIF tricky or impossible to use.
But I'd like to keep this text in there to keep the invoice looking simple; if possible.
B) NOT being able to SPLIT a range for input into a simple SUMIF.
C) Half the IF( solutions I've tried needs a RANGE value rather than my ArrayFormula workaround, so I'm stuck.

some of my thinking so far...

FXN-KNOWLEDGE THAT MAY BE NEEDED (any combination of these that works)

I wouldn't mind using the actual SUBTOTALS fxn instead of SUMIF( "<>sub*" if that solves the problem.
STILL, I realize my #2 GOAL is counter-intuitive since SUBTOTAL fxn is designed to NOT count other subtotals, so we'll probably need some variation of Sum(If( since SumIf( doesn't seem to be working for this purpose.

using OFFSET, I believe, would be too rigid, since it's not the same number of rows offset every time. 

I'm a musician, not a developer, so I can't (currently) wrap my head around google Scripts, So any way that this could be solved with a FXN in Sheets would be AMAZING.

Thank you THANK you!  :)
1 Automatically Reset Subtotal Ranges. Here's What it Should Look Like.png
229 KB
2 My Workaround = Grand Total minus the Sum of All previous subtotals .png
188 KB
3 Example to Work with.png
278 KB

No comments:

Post a Comment