*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

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).