# Value error in array formula

#### domtrump

##### Board Regular
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.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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?

Last edited:
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.

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!

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)

Replies
4
Views
217
Replies
0
Views
133
Replies
1
Views
296
Replies
2
Views
373
Replies
6
Views
206

1,203,610
Messages
6,056,296
Members
444,855
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back