SUMIF returns #VALUE

[Harold Garcia]

Good day,

I have a problem with this formula when the sourcedata is coming from different excel file. Is there any way that once data is capture and saved, there will be messages prompting if i will update or dont update the data when i re open it?

Thanks

 

[dispelthemyth]

You would be better off using Sumproduct as i dont think you can get a warning when opening a file with Sumif, i think you will automatically get an error if the other workbook is closed


Show what your formula is and an alternative may be found for you

 

[PNicholson]

I am getting a #VALUE! Error using SUMIF in the same workbook. Other SUMIF functions are working correctly, just not this one.

Here is the function - =SUMIF(SECTION,S13,Net_360)

SECTION is a named range on another tab in the same workbook
Cell S13 is just an A. This same function is also in the spreadsheet - using a different cell (S14 for example) and it works just fine.
Net_360 is a named range also on another tab in the same workbook

This one works - =SUMIF(SECTION,S14,Net_360)
This one doesn't - =SUMIF(SECTION,S13,Net_360)

The named range SECTION does have data in it with a value of A (which is what is in cell S13 in the formula)

Hope this all makes sense.

Help anyone?

 

[Aladin Akyurek]

I am getting a #VALUE! Error using SUMIF in the same workbook. Other SUMIF functions are working correctly, just not this one.

Here is the function - =SUMIF(SECTION,S13,Net_360)

SECTION is a named range on another tab in the same workbook
Cell S13 is just an A. This same function is also in the spreadsheet - using a different cell (S14 for example) and it works just fine.
Net_360 is a named range also on another tab in the same workbook

This one works - =SUMIF(SECTION,S14,Net_360)
This one doesn't - =SUMIF(SECTION,S13,Net_360)

The named range SECTION does have data in it with a value of A (which is what is in cell S13 in the formula)

Hope this all makes sense.

Help anyone?

What do you get with:

=ROWS(SECTION)=ROWS(Net_360)

 

[PNicholson]

The result is TRUE

 

[Jonmo1]

With this statement...

This one works - =SUMIF(SECTION,S14,Net_360)
This one doesn't - =SUMIF(SECTION,S13,Net_360)

It likely means that there are #VALUE! errors within the Net_360 range, in one or more of the rows that the Section range matches S13.

 

[PNicholson]

With this statement...

It likely means that there are #VALUE! errors within the Net_360 range, in one or more of the rows that the Section range matches S13.

EXCELLENT Catch. Have NOOO idea why I didn't think of that. There was ONE record (out of 16,000) that had a bad piece of data.

Thanks SOOOO much for your help - its greatly appreciated.

 

[Jonmo1]

Glad to help, thanks for the feedback.