Sunday, August 20, 2017

Google Spreadsheet Post #2229

Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI Aug-20-2017
question by: Henrique Magalhaes

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

Array formula with sumif with less than condition

Hi, I have this sheet with the following columns of data:

Column A - Date: this is the date of a transaction.

Column B - Type: this is the type of the item

Column C- Value: this is the value of the transaction

For each row, I need to determine 2 values:

Column D. the sum of all values of the current type, where date is equal the current date. I've used the following formula to achieve that: sumifs(C:C;A:A;A2;B:B;B2).

Column E. the sum of all values of the current type, where date is less than or equal the current date. I've used the following formula to achieve that: sumifs(C:C;A:A;"<="&A2;B:B;B2).

However this sheet will be used with a great amount of data, and I need it to auto populate new rows with these formulas. Arrayformula does that well for the first one, and I changed it to: =arrayformula(if(row(A:A)=1;"sum of type in current date";sumif(A:A&B:B;A:A&B:B;C:C))). Sumifs didn't work so I changed it to a formula with sumif and & operators.

However I could not think how to make it for the column E formula. Is there any way to use arrayformula with sumif (or sumifs) and "<=" condition? If not, is there any other way to auto populate the formula to new lines?

Thanks