# how do I add isblank(ref) criteria to arrayformula?

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

Here is the link to see this live.

-
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
thanks, fixed this. – getthemike Feb 7 '13 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 '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. – getthemike Feb 15 '13 at 22:05

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)

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

-
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. – getthemike Feb 11 '13 at 22:45

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))
``````
-
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. – joseph4tw Feb 10 '13 at 21:14
@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 '13 at 21:18

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!

-
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. – getthemike Feb 12 '13 at 8:57
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. – getthemike Feb 12 '13 at 17:20