Getting weird 00/01/1900 response to sumifs

yusuo

New Member
Joined
Feb 6, 2018
Messages
3
Im trying to write a sumifs for the first time and i have the cells basically formatted to a date, now whenever excel is unable to find the criteria its reporting back a response of 00/01/1900, however this is just a stupid response so I am hoping to get excel to say either NA or report back blank.

Im out of my depth here and I don't really know where to go, can anyone help, here is the formula below

=SUMIFS(Sheet3!H:H, Sheet3!C:C,Sheet1!A14, Sheet3!B:B, "Severn Trent Water")

As you can see its reading a whole column from a different sheet and corresponding the answer to a reference number on the first sheet and if the number and the keyword are present it reports back a specific date, usually the date ive received the paperwork
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
It is not stupid at all. If It can't rturn any value, it will return FALSE which is simply 0. If you format 0 as date, this is the value you'll have.
 
Upvote 0
Try customers formatting the cell as dd/mm/yyyy;; or [=0]"";General rather than altering the formula with another function call.
 
Last edited:
Upvote 0
How would I go about formatiing 0, its a spreadsheet for work and i cannot amend to much as the date means something to someone else.

Would there be something I could add to the formula to result in it stating NA instead of the 0 date it is currently showing
 
Upvote 0
Perhaps use:

=IFERROR(1/(1/SUMIFS(Sheet3!H:H, Sheet3!C:C,Sheet1!A14, Sheet3!B:B, "Severn Trent Water")),"NA")
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top