Getting weird 00/01/1900 response to sumifs

[yusuo]

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

 

[Legacy 143009]

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.

 

[MARK858]

Try customers formatting the cell as dd/mm/yyyy;; or [=0]"";General rather than altering the formula with another function call.

 

[yusuo]

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

 

[RoryA]

Perhaps use:

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

 

[yusuo]

Perhaps use:

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

Thank you, this worked perfectly