With almost universal availability of Internet and the availability of reliable on-line productivity tools, such as Google Docs, individuals as well as companies are switching over to Cloud Computing. In this blog I will post items of interest to my colleagues/patrons/clients.
Saturday, December 30, 2017
yogi_A Row By Row Single Formula For Line Item Costs And SubTotals To Be Dragged Down For Copying
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.
OFFSET: using OFFSET, I believe, would be too rigid, since it's not the same number of rows offset every time.