ArrayFormula Only Iterating The First Value Throughout the Column, Need All the Values that Fit the Criteria

Question

The problem:

I have two sheets, Auto Expenses & Business Expenses (duplicate sheets with different categories of expenses) Column J represents the cost of an expenses Column F represents the date of the expense

I am trying to create a function that will add up all of the expenses between both sheets given a start date & an end date and iterate down the column

Here is the function I wrote:

=ArrayFormula(if(isblank(F2:F),"",SUMIFS('Auto Expenses'!$J$7:$J,'Auto Expenses'!$F$7:$F,">="&O2:O,'Auto Expenses'!$F$7:$F,"<="&P2:P)+SUMIFS('Business Expenses'!$J$7:$J,'Business Expenses'!$F$7:$F,">="&O2:O,'Business Expenses'!$F$7:$F, "<="&P2:P)))

O2:O is a list of start dates & P2:P is a list of end dates>

When I run the function, it will only return the first sum of the expenses and iterate all the way down with the same value.

What am I doing wrong?

If I use this function instead and drag is down, it gives the proper results, but I want it to iterate down the column automatically

=SUMIFS('Auto Expenses'!$J$7:$J,'Auto Expenses'!$F$7:$F,">="&O2,'Auto Expenses'!$F$7:$F, "<="&P2)+SUMIFS('Business Expenses'!$J$7:$J,'Business Expenses'!$F$7:$F,">="&O2,'Business Expenses'!$F$7:$F, "<="&P2)

Here is a link to a copy of the spreadsheet for reference: https://docs.google.com/spreadsheets/d/1Lbrvsrz90RSYhedwA8IkyoYlHqWy5Lw7Ngxw5eRjAKs/edit?usp=sharing[enter image description here][1]

@TomSharpe I have granted access to the spreadsheet now. You should be able to view & edit — Evans Raymond
– Evans Raymond, Commented Dec 3, 2021 at 14:38
@idfurw oh I forgot about sumifs not being supported by arrayformula. So then would I have to do a sumif for each condition and just add them all together? — Evans Raymond
– Evans Raymond, Commented Dec 3, 2021 at 14:39

Tom Sharpe · Accepted Answer · 2021-12-03 18:21:22Z

0

Since your time intervals are all seven days, one approach is to develop a 2d array with 7 columns where each column represents a different day of the week, and use that in a sumif to get the totals for each day. Then use Mmult to sum across the 7 days:

=ArrayFormula(if(O2:O="",,mmult(sumif('Auto Expenses'!F7:F,O2:O+sequence(1,7,0),'Auto Expenses'!J7:J),sequence(7,1,1,0))))

Then do the same thing for business expenses (which isn't yet populated in your sample sheet).

answered Dec 3, 2021 at 18:21

Tom Sharpe

35.1k5 gold badges29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Evans Raymond Over a year ago

Wow I didn't even know =SEQUENCE was a function I could use! I'm new to learning about Google Sheets/Excel arrays so this is very helpful to know. Thank you

Collectives™ on Stack Overflow

ArrayFormula Only Iterating The First Value Throughout the Column, Need All the Values that Fit the Criteria

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related