Tell me more ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.
up vote 2 down vote favorite
1

EDIT: Self-Answered

This is an improvement of work from this post.

I have an array formula that is similar to this image. 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. Any help appreciated.

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

Google Spreadsheet Image with formula

Here is the link to see this live.

share|improve this question
The link to the doc doesn't work- says "That is not a valid spreadsheet URL." – Jacob Jan Tuinstra Feb 7 at 18:21
thanks, fixed this. – lazfish Feb 7 at 19:38
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? – joseph4tw Feb 10 at 3:45
I've added an IF THEN ELSE to the query formula. See playground sheet. – Jacob Jan Tuinstra Feb 14 at 10:53
Not sure what you mean. Added comments to spreadsheet to explain. – lazfish Feb 15 at 22:05

3 Answers

up vote 2 down vote

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

share|improve this answer
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. – lazfish Feb 11 at 22:45
up vote 1 down vote

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))
share|improve this answer
You're missing out on due dates 8,9,10. The result of your query yields only one value? – Jacob Jan Tuinstra Feb 10 at 6:57
@JacobJanTuinstra, good point. Thanks for letting me know. I have fixed the error. – joseph4tw Feb 10 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 – joseph4tw Feb 10 at 21:18
up vote -1 down vote accepted

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!

share|improve this answer
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 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. – lazfish Feb 12 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 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. – lazfish Feb 12 at 17:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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