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

question by: Tom Stroller

https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/UgOj2k9VtL0;context-place=mydiscussions

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.

SPREADSHEET (view only)

https://docs.google.com/spreadsheets/d/1X0A56KhoZVHdWf-pqtt6tgmHAnLhHqhLdwVTIDIq63I/edit?usp=sharing

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! :)