# SUMIF returns #VALUE

#### Harold Garcia

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

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### dispelthemyth

##### Well-known Member
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

##### New Member
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?

##### MrExcel MVP
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

##### New Member
The result is TRUE

#### Jonmo1

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
Glad to help, thanks for the feedback.

Replies
4
Views
246
Replies
1
Views
70
Replies
1
Views
135
Replies
2
Views
314
Replies
0
Views
147

1,195,582
Messages
6,010,577
Members
441,557
Latest member
Jbest23

### 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