Sum array #value! Error

[chrisboughter]

I use this formula (see below) in a report I run every month. I have been using it in the same way for years. Now, all of a sudden, I am getting a #VALUE! error. I format everything the same every time I run the report. I have even started this report from scratch twice to make sure it wasn't something else from another sheet or a formatting issue causing the error.

The formula (except for today) sums column C of the 'WYT3' tab when column B of the 'All Exceptions' tab matches column C of 'WYT3' and column F of 'All Exceptions' matches column A of 'WYT3'.

"=SUM(('WYT3'!$C$2:$C$###)*(B2='WYT3'!$C$2:$C$###)*(F2='WYT3'!$A$2:$A$###))" [in this example ###=1701 on 'WYT3'

[daddylonglegs]

Are you sure that's right? You have column C as both criteria column and the column to sum, that's a little unusual......

Which version of Excel are you using? If it's Excel 2007 or later SUMIFS would probably be better for you

[chrisboughter]

Hi daddylonglegs,

Thanks for the reply. I found out what the problem was. If you look at column C being summed on tab 'WYT3' I found a non number value in cell C1372. Funny thing is, it will sum column C of tab 'WYT3' by itself but when you add the 2nd test it fails. I took out the non-number value and the forumula works perfectly as it always.

Thanks,
Chris