2

This is an improvement of work from this post.

I have an array formula that is similar to this image:

Google Spreadsheet Image with formula

It sums column A when the date in F matches the due date (C). What I want to do is only sum A when F matches C and B is blank. This way when I add a date into the "Paid On" Column, G and H will no longer reflect that payment as "needed".

This would make my budget sheet much better.

4
  • The link to the doc doesn't work- says "That is not a valid spreadsheet URL." – Jacob Jan Tuinstra Feb 7 '13 at 18:21
  • so, when you say "When F matches C" do you mean in the same row? Or do you mean when C's value is found in F? – Joseph Feb 10 '13 at 3:45
  • I've added an IF THEN ELSE to the query formula. See playground sheet. – Jacob Jan Tuinstra Feb 14 '13 at 10:53
  • Not sure what you mean. Added comments to spreadsheet to explain. – gooddadmike Feb 15 '13 at 22:05
2

I've created a new sheet in the spreadsheet your working in, called "playground". There I started playing around and ended up with a QUERY formula. First I reproduced your result:

=QUERY(A:D;"SELECT C, SUM(A) GROUP BY C PIVOT D") (See F2)

Secondly I added your request:

=QUERY(A:D;"SELECT C, SUM(A) WHERE B IS NULL GROUP BY C PIVOT D") (See I2)

UPDATE
If you start adding rows, the result is getting odd; an extra blank row and column is added. I had to re-arrange the query a bit to adjust for that:

=QUERY(A:D;"SELECT C, SUM(A) WHERE (D IS NOT NULL AND C IS NOT NULL AND A IS NOT NULL AND B IS NULL) GROUP BY C PIVOT D")

UPDATE 12-02-2013 To complete the answering, I've updated the result with the following query:
=QUERY(A:B;"SELECT SUM(A) WHERE(B IS NOT NULL) LABEL SUM(A) 'Total Amount Due'")

The QUERY function is extremely powerful and with a bit of SQL knowledge easy to use !!

See references for help (or asks again):

1
  • This is really good. It almost does what I want. There are some odd things about it. The biggest problem is that all totals disappear when B3:B is blank. I like the way it eliminates dates where nothing is due. – gooddadmike Feb 11 '13 at 22:45
1

Another approach:

I think this will help:

=ARRAYFORMULA(SUMPRODUCT($B$2:$B$8<>"",IF(ISERROR(MATCH($C$2:$C$8,$F$2:$F$11,0)),false,true),$A$2:$A$8))
3
  • You're missing out on due dates 8,9,10. The result of your query yields only one value? – Jacob Jan Tuinstra Feb 10 '13 at 6:57
  • @JacobJanTuinstra, good point. Thanks for letting me know. I have fixed the error. – Joseph Feb 10 '13 at 21:14
  • 1
    @JacobJanTuinstra, About returning one value, yes, that was my intention. I thought the OP's requested to do a sum based on his conditions, which I thought he meant to return the sum only, not the rows that match. BTW, +1 to your answer. I had no clue Google Spreadsheets could do queries like that – Joseph Feb 10 '13 at 21:18
-1

I found the solution. @JacobJanTuinstra - I never realized how easy it was to add SQL code in excel. Thanks for this. @joseph4tw - your answer worked great for a single value.

See my live preview for the solution.

=arrayformula(sumif(C2:C8 & D2:D8 & B2:B8,F2:F11 & G1 & "",A2:A8))

It was so easy I don't know why I didn't try it before. Thanks for your help!

4
  • So, you basically want to have the sum of the due amount based on payment dates: =SUM(FILTER(A3:A9;B3:B9<>"")). I don't see how this can be influenced by the other logic you're adding... It's more efficient to add this type of information to the question. – Jacob Jan Tuinstra Feb 12 '13 at 7:16
  • Not sure I understand your comment. I want to know how much need to be in each account on each day. As payments post I want to add the date and have the action of adding that date cause the amount due to be removed from the list. – gooddadmike Feb 12 '13 at 8:57
  • 1
    Have a look at the playground sheet. Is this what you want? I'm close to down voting your answer btw. – Jacob Jan Tuinstra Feb 12 '13 at 9:24
  • Yes. This is exactly what I want and is better than my solution in every way except that All amounts should show when no dates are entered. Currently no amounts show if I remove all text from column B. – gooddadmike Feb 12 '13 at 17:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.