Value error in array formula

[domtrump]

I am trying to use the following array formula but I am getting a #VALUE error:

{=SUM((MONTH(B$5)=MONTH('Other Expenses'!$A:$A))*(B$6='Other Expenses'!B:B)*('Other Expenses'!D:D))}

Here is what I'm trying to do:

I want to sum up the values in column D of the 'Other Expenses' worksheet whenever the month of the date in b5 of the current worksheet matches the month of the date in column A of the 'Other Expenses' worksheet AND the text in b6 of the current worksheet matches the text in column B of the 'Other Expenses' worksheet.

Can someone suggest a formula that might do this trick? Thanks.

 

[Jonmo1]

Could be one of several possible causes.

1. Which version of Excel do you have?
If you have 2003 or earlier, you can't use entire column references like A:A, you must specify row #s like A1:A100

2. Not sure it's the problem, but your expressions should be reversed..
The multicell range comes first.
Should be like B1:B100=B6,
NOT like B6=B1:B100

3. Your dates in the other sheet column A (or in B5) are not really dates.
Test with ISNUMBER
=ISNUMBER(B5)
=ISNUMBER('Other Expenses'!A2) <- where A2 is a date you believe to be a match to B5.
What do those return?

 

[Marcelo Branco]

First i would suggest using definite ranges. So assuming your data in A1:D100 with headers in row 1, maybe something like

=SUMPRODUCT(--(MONTH('Other Expenses'!A2:A100)=MONTH(B5)),--('Other Expenses'!B2:B100=B6),'Other Expenses'!D2:D100)

You need real dates in column A of Other Expenses and in B5

M.

 

[domtrump]

Thanks to you both for your help. After some experimentation, I got the array formula right:

{=SUM((MONTH('Other Expenses'!$A$2:$A$10000)=MONTH(B$5))*('Other Expenses'!$B$2:$B$10000=B$6)*('Other Expenses'!$D$2:$D$10000))}

It was my headers in row 1 that were causing the #Value error. Using definite ranges and excluding row 1 did the trick.

Thanks!

 

[barry houdini]

In Excel 2007 or later it's possibly better to use SUMIFS

=SUMIFS('Other Expenses'!D:D,'Other Expenses'!$A:$A,">"&B$5-DAY(B$5),'Other Expenses'!$A:$A,"<="&EOMONTH(B$5,0),'Other Expenses'!B:B,B$6)