Google Sheets Array Formula how to specify which columns to increment and which not to?
up vote
-1
down vote
favorite
I have a google sheet with & columns: ,User,Purchase,Date,billing date which is being populated from a google form.
I want the sum of pending expenses for each user.
Billing date is also specific to user.
So far, I populate column F with last billing date with =MAX(FILTER(D:D, A:A = E2)) and then use this for calculating the pending bill using =IFError(SUM(Filter(C2:C,B2:B>F2,A2:A=E2) ),0)
How do I calculate column F and G using Array Formula?
Link to google sheet: https://docs.google.com/spreadsheets/d/1g6zHXq_p5yX_6vlco1hTyWWt6eC_wSP-9AI5iLWZNnA/edit?usp=sharing
google-sheets array-formulas
asked Nov 7 at 21:56
SBob
1
add a comment |
up vote
-1
down vote
favorite
I have a google sheet with & columns: ,User,Purchase,Date,billing date which is being populated from a google form.
I want the sum of pending expenses for each user.
Billing date is also specific to user.
So far, I populate column F with last billing date with =MAX(FILTER(D:D, A:A = E2)) and then use this for calculating the pending bill using =IFError(SUM(Filter(C2:C,B2:B>F2,A2:A=E2) ),0)
How do I calculate column F and G using Array Formula?
Link to google sheet: https://docs.google.com/spreadsheets/d/1g6zHXq_p5yX_6vlco1hTyWWt6eC_wSP-9AI5iLWZNnA/edit?usp=sharing
google-sheets array-formulas
asked Nov 7 at 21:56
SBob
1
The problem is that aggregate functions likemaxandsumcannot be used witharrayformula. Neither canfilter, which is itself an arrayformula. If you can write a script, solve your problem that way. Otherwise, there may be formula workarounds, but usually end up with formulas that are unreadable by normal humans!
– bcperth
Nov 8 at 3:20
add a comment |
up vote
-1
down vote
favorite
up vote
-1
down vote
favorite
I have a google sheet with & columns: ,User,Purchase,Date,billing date which is being populated from a google form.
I want the sum of pending expenses for each user.
Billing date is also specific to user.
So far, I populate column F with last billing date with =MAX(FILTER(D:D, A:A = E2)) and then use this for calculating the pending bill using =IFError(SUM(Filter(C2:C,B2:B>F2,A2:A=E2) ),0)
How do I calculate column F and G using Array Formula?
Link to google sheet: https://docs.google.com/spreadsheets/d/1g6zHXq_p5yX_6vlco1hTyWWt6eC_wSP-9AI5iLWZNnA/edit?usp=sharing
google-sheets array-formulas
asked Nov 7 at 21:56
SBob
1
I have a google sheet with & columns: ,User,Purchase,Date,billing date which is being populated from a google form.
I want the sum of pending expenses for each user.
Billing date is also specific to user.
So far, I populate column F with last billing date with =MAX(FILTER(D:D, A:A = E2)) and then use this for calculating the pending bill using =IFError(SUM(Filter(C2:C,B2:B>F2,A2:A=E2) ),0)
How do I calculate column F and G using Array Formula?
Link to google sheet: https://docs.google.com/spreadsheets/d/1g6zHXq_p5yX_6vlco1hTyWWt6eC_wSP-9AI5iLWZNnA/edit?usp=sharing
google-sheets array-formulas
google-sheets array-formulas
asked Nov 7 at 21:56
SBob
1
asked Nov 7 at 21:56
SBob
1
asked Nov 7 at 21:56
SBob
1
asked Nov 7 at 21:56
SBob
1
asked Nov 7 at 21:56
SBob
1
1
The problem is that aggregate functions likemaxandsumcannot be used witharrayformula. Neither canfilter, which is itself an arrayformula. If you can write a script, solve your problem that way. Otherwise, there may be formula workarounds, but usually end up with formulas that are unreadable by normal humans!
– bcperth
Nov 8 at 3:20
add a comment |
The problem is that aggregate functions likemaxandsumcannot be used witharrayformula. Neither canfilter, which is itself an arrayformula. If you can write a script, solve your problem that way. Otherwise, there may be formula workarounds, but usually end up with formulas that are unreadable by normal humans!
– bcperth
Nov 8 at 3:20
The problem is that aggregate functions like
max and sum cannot be used with arrayformula. Neither can filter, which is itself an arrayformula. If you can write a script, solve your problem that way. Otherwise, there may be formula workarounds, but usually end up with formulas that are unreadable by normal humans!– bcperth
Nov 8 at 3:20
The problem is that aggregate functions like
max and sum cannot be used with arrayformula. Neither can filter, which is itself an arrayformula. If you can write a script, solve your problem that way. Otherwise, there may be formula workarounds, but usually end up with formulas that are unreadable by normal humans!– bcperth
Nov 8 at 3:20
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
You need to have a look at the fantastic Query function.
Something similar to this seems to work: =QUERY(A2:D,"select A, max(D), sum(C) group by A") but you will need to nest two queries to get your desired result with the sum.
(note though that in your sheet, you are showing a value for last bill even if a bill hasn't been sent)
answered Nov 8 at 10:37
a-burge
452511
Yeah that's a default value and it still meets the condition so I might just leave it there I have tried group by A,F as well but that is not producing the write results
– SBob
Nov 8 at 14:59
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
You need to have a look at the fantastic Query function.
Something similar to this seems to work: =QUERY(A2:D,"select A, max(D), sum(C) group by A") but you will need to nest two queries to get your desired result with the sum.
(note though that in your sheet, you are showing a value for last bill even if a bill hasn't been sent)
answered Nov 8 at 10:37
a-burge
452511
Yeah that's a default value and it still meets the condition so I might just leave it there I have tried group by A,F as well but that is not producing the write results
– SBob
Nov 8 at 14:59
add a comment |
up vote
0
down vote
You need to have a look at the fantastic Query function.
Something similar to this seems to work: =QUERY(A2:D,"select A, max(D), sum(C) group by A") but you will need to nest two queries to get your desired result with the sum.
(note though that in your sheet, you are showing a value for last bill even if a bill hasn't been sent)
answered Nov 8 at 10:37
a-burge
452511
Yeah that's a default value and it still meets the condition so I might just leave it there I have tried group by A,F as well but that is not producing the write results
– SBob
Nov 8 at 14:59
add a comment |
up vote
0
down vote
up vote
0
down vote
You need to have a look at the fantastic Query function.
Something similar to this seems to work: =QUERY(A2:D,"select A, max(D), sum(C) group by A") but you will need to nest two queries to get your desired result with the sum.
(note though that in your sheet, you are showing a value for last bill even if a bill hasn't been sent)
answered Nov 8 at 10:37
a-burge
452511
You need to have a look at the fantastic Query function.
Something similar to this seems to work: =QUERY(A2:D,"select A, max(D), sum(C) group by A") but you will need to nest two queries to get your desired result with the sum.
(note though that in your sheet, you are showing a value for last bill even if a bill hasn't been sent)
answered Nov 8 at 10:37
a-burge
452511
answered Nov 8 at 10:37
a-burge
452511
answered Nov 8 at 10:37
a-burge
452511
answered Nov 8 at 10:37
a-burge
452511
452511
Yeah that's a default value and it still meets the condition so I might just leave it there I have tried group by A,F as well but that is not producing the write results
– SBob
Nov 8 at 14:59
add a comment |
Yeah that's a default value and it still meets the condition so I might just leave it there I have tried group by A,F as well but that is not producing the write results
– SBob
Nov 8 at 14:59
Yeah that's a default value and it still meets the condition so I might just leave it there I have tried group by A,F as well but that is not producing the write results
– SBob
Nov 8 at 14:59
Yeah that's a default value and it still meets the condition so I might just leave it there I have tried group by A,F as well but that is not producing the write results
– SBob
Nov 8 at 14:59
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53198450%2fgoogle-sheets-array-formula-how-to-specify-which-columns-to-increment-and-which%23new-answer', 'question_page');
}
);
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
The problem is that aggregate functions like
maxandsumcannot be used witharrayformula. Neither canfilter, which is itself an arrayformula. If you can write a script, solve your problem that way. Otherwise, there may be formula workarounds, but usually end up with formulas that are unreadable by normal humans!– bcperth
Nov 8 at 3:20