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

DYNAMIC SUMIF/SUBTOTAL RANGES

Hi there & Happy New Year to ya! :)

INVOICE SOLUTION:
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...

GOAL:
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.


PROBLEMS:
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)
SUM( IF( INDEX( MATCH( SEARCH( SUMIF( MATCH( SUBTOTAL(?  QUERY(? ARRAYFORMULA(
ISTEXT( ISNUMBER( SPLIT(


SUBTOTALS:
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.

OFFSET:
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