CODEWITHSUNDEEP
google-sheetssplitpivotgoogle-sheets-formulagoogle-query-language
lvicks
lvicks

Reputation: 745

ARRAYFORMULA is only populating the first row

I have financial data that I am trying to summarize in a format that can be used by a line chart.

The example spreadsheet is here.

In my source data on the left, I have an entry per Date, Symbol, Account. I need to transform this data so there is a row per Date and a column for each Symbol. I will SUM Total Value regardless of account.

I found a way to pull a unique Date (see H2), and then transpose unique Symbols into columns (see I1).

I also found a way to use SUMIFS to get the aggregation I want (take a look at cell I2), but I can't figure out how to use ARRAYFORMULA to apply this value to all rows in column I.

I know I can drag my formula from I2 down to I3,I4,I.. etc, but this sheet is part of a larger project so I'd like it to auto-populate as dates are added to H.

From what I've read ARRAYFORMULA should apply the formula to multiple rows. What am I missing?

Thanks

Upvotes: 3

Views: 1084

Answers (2)

Osm
Osm

Reputation: 2891

Use formulas like this

=ARRAYFORMULA(IF(H2:H="",,SUMIFS($F$2:$F, $A$2:$A, $H2, $B$2:$B, I$1)))

Add IF(H2:H="",,

Explanation
if the range is empty "" do nothing ,, else Your formula

Your Example

Cells Formulas
I2 =ARRAYFORMULA(IF(H2:H="",,SUMIFS($F$2:$F, $A$2:$A, $H2:H, $B$2:$B, I$1)))
J2 =ARRAYFORMULA(IF(H2:H="",,SUMIFS($F$2:$F, $A$2:$A, $H2:H, $B$2:$B, J$1)))
K2 =ARRAYFORMULA(IF(H2:H="",,SUMIFS($F$2:$F, $A$2:$A, $H2:H, $B$2:$B, K$1)))

Upvotes: 1

player0
player0

Reputation: 1

use:

=QUERY(A1:F, "select A,sum(F) where A is not null group by A pivot B", 1)

enter image description here

Upvotes: 1

Related Questions