sumproduct returns #value!

[spig]

I have the following formula:

=SUMPRODUCT(--(C$1:CW$1-DAY(C$1:CW$1)+1=$DD$9),C10:CW10)

If it reads from an empty cell, it works fine but it is reading from a cell which itself contains an if formula and therefore returns #VALUE! which i don't want. I want it to return a 0 or the actual number but not a #VALUE!

How do I alter this formula to avoid #VALUE! being returned?

Can anyone help me please - I can copy spreadsheet details if necessary.

Thanks

 

[gsistek]

Did you use Ctrl-Shift-Enter to enter the formula? Are you sure the cells are formatted the same?

 

[texasalynn]

try this

=IF(ISERROR(SUMPRODUCT(--(C$1:CW$1-DAY(C$1:CW$1)+1=$DD$9),C10:CW10)),0,SUMPRODUCT(--(C$1:CW$1-DAY(C$1:CW$1)+1=$DD$9),C10:CW10))

 

[Jonmo1]

It doesn't return #VALUE! simply because the cells contain a formula.

It's probably because those formulas are not returning real dates...

Can you post that IF formula?

 

[T. Valko]

I have the following formula:

=SUMPRODUCT(--(C$1:CW$1-DAY(C$1:CW$1)+1=$DD$9),C10:CW10)

If it reads from an empty cell, it works fine but it is reading from a cell which itself contains an if formula and therefore returns #VALUE! which i don't want. I want it to return a 0 or the actual number but not a #VALUE!

How do I alter this formula to avoid #VALUE! being returned?

Can anyone help me please - I can copy spreadsheet details if necessary.

Thanks

The #VALUE! error means there is/are probably TEXT values in the range C$1:CW$1.

DAY("text") = #VALUE!

Make sure your dates are true Excel dates. True Excel dates are really just numbers formatted to look like dates.

=ISNUMBER(C1)

If the cell contains a true Excel date then that formula will return TRUE.

 

[spig]

Hi,

Here is if formula reading from another identical tab

=IF($A$1="","",IF(C2="","",IF('Birds Seen'!C10="","",'Birds Seen'!C10)))

I am entering all birds seen in 1 tab then another tab selects records from the original tab only if the selected location is shown in cell A1 of the current tab. I can send spreadsheet if needed. C2 in this case is asking whether chosen location = orignial tab entry location and if it isn't, return "" but if it is, return number of birds seen for that category

Thanks

 

[Jonmo1]

It's because the formula is returning ""
That is technically not blank, it's a null text string, causing the error.

Try making it return 0 instead of ""
=IF($A$1="",0,IF(C2="",0,IF('Birds Seen'!C10="",0,'Birds Seen'!C10)))

 

[T. Valko]

Hi,

Here is if formula reading from another identical tab

=IF($A$1="","",IF(C2="","",IF('Birds Seen'!C10="","",'Birds Seen'!C10)))

I am entering all birds seen in 1 tab then another tab selects records from the original tab only if the selected location is shown in cell A1 of the current tab. I can send spreadsheet if needed. C2 in this case is asking whether chosen location = orignial tab entry location and if it isn't, return "" but if it is, return number of birds seen for that category

Thanks

Ok, try it like this:

=SUMPRODUCT(--(TEXT(C$1:CW$1,"mmmyyyy")=TEXT($DD$9,"mmmyyyy")),C10:CW10)

 

[Aladin Akyurek]

Hi,

Here is if formula reading from another identical tab

=IF($A$1="","",IF(C2="","",IF('Birds Seen'!C10="","",'Birds Seen'!C10)))

I am entering all birds seen in 1 tab then another tab selects records from the original tab only if the selected location is shown in cell A1 of the current tab. I can send spreadsheet if needed. C2 in this case is asking whether chosen location = orignial tab entry location and if it isn't, return "" but if it is, return number of birds seen for that category

Thanks

As Jonmo1 pointed out, the formula blanks (text values) are the culprit. You can actually switch to a more general type of formula:

Control+shift+enter, not just enter...

=SUM(
    IF(ISNUMBER(C$1:CW$1),
    IF(C$1:CW$1-DAY(C$1:CW$1)+1=$DD$9,
     C10:CW10)))

 

[spig]

Hi,

I changed all formulas to read 0 instead of "" but it now shows dates as 00-Jan-00 - how do I get rid of that so cells are clear? In excel 2003, you could go to tools and options and remove zero's. I am working on excel 2007. However, it did resolve the #VALUE! issue so thanks for that

Regards

David