Excel 2007 : Sumproduct to only add dates before the 15th of month?

[Sgt_Utz]

I'm hoping you all can help me. I'm on vacation for a few weeks and have literally spent the last two days doing nothing but research and failing to get my function to work!!

It's for my monthly budget. Each month I put in the due dates for each of my bills. I always have to temporarily add the bills that will be due before the 15th so I can see how much money I really have until I get paid on the 15th. I'm trying to create a cell that will do these calculations for me by looking at the date in the "DUE" column, and if it is before the 15th, sum the "BILL" column.

I got this to work except for two problems... I want to put "N/A" on the "DUE" column for bills that I'm not paying for that month, but this causes the formula to return #VALUE!. I do know why it does this, but I can't get "ISERROR" to work correctly (returns one value for the entire array). The other problem is that if the cell is blank it adds it (because it's less than 15)

What am I doing wrong? I know I'm SO close, but I'm also a VBscripter and know that ONE character can corrupt the entire script.

Here's my formula:
=SUMPRODUCT((DAY(I27:I30)<15)*(H27:H30))

[Haseeb Avarakkan]

Try this Array Formula,

=SUM(IF(ISNUMBER(1/I27:I30),IF(DAY(I27:I30)<15,H27:H30)))

Confirmed with CONTROL+SHIFT+ENTER, rather than just ENTER.

[JBeaucaire]

I would add a column where I entered a "skip" text entry, leaving the dates unaltered.

=SUMPRODUCT((H27:H30="")*(DAY(I27:I30)<15)*(J27:J30))

[Sgt_Utz]

Try this Array Formula,

=SUM(IF(ISNUMBER(1/I27:I30),IF(DAY(I27:I30)<15,H27:H30)))

Confirmed with CONTROL+SHIFT+ENTER, rather than just ENTER.

Thanks for the replies. This is more what I'm looking for. It worked for a second, and when looking at the evaluation to see how it worked I saw how it arrayed the "1/I27:I30".

It worked and evaluated as "1/I27:I30" but now it's evaluating as "1/I27:I30" and now returning a 0 no matter what. What did I change? It's evaluating the I27:I30 as a single return instead of an array...

[daddylonglegs]

Haseeb's formula works for me - did you confirm with CTRL+SHIFT+ENTER? If done correctly you'll see curly braces like { and } around the formula in the formula bar.

Here's an alternative "non-array" version

=SUMPRODUCT(ISNUMBER(MATCH(DAY(I27:I30),{1,2,3,4,5,6,7,8,9,10,11,12,13,14},0))+0,H27:H30)

[Sgt_Utz]

Haseeb's formula works for me - did you confirm with CTRL+SHIFT+ENTER? If done correctly you'll see curly braces like { and } around the formula in the formula bar.

Here's an alternative "non-array" version

=SUMPRODUCT(ISNUMBER(MATCH(DAY(I27:I30),{1,2,3,4,5,6,7,8,9,10,11,12,13,14},0))+0,H27:H30)

I looked up what this "control shift enter" thing was and am now edumacated on what that means. That was the problem too! Thanks guys, I'm sure I'll be posting on here again soon.