Sumif to show blank as blank or 0 =SUMIFS(Sheet2!C2:C367,Sheet2!A2:A367,A3,Sheet2!B2:B367,B14)

[Luke1690]

Hi All,

I'm using the below formula to show a date when a Day is selected in a range and week.

=SUMIFS(Sheet2!C2:C367,Sheet2!A2:A367,A3,Sheet2!B2:B367,B14)

if a day hasn't been selected and cell B14 is empty, it automatically puts 00/01/1900

how can i make it blank or at least a 0 when no data has been entered?

Thanks for your support.

 

[Joe4]

It is returning zero - that is what the date 00/01/1900.

It is important to understand how Excel stores dates - it sees them as numbers, literally the number of days since 00/01/1900.
So all dates in Excel really are are numbers with a special date format. You can easily see this by changing the format of any valid date in Excel to "General", and then you will see the date as Excel does.

If you set a Custom Format on the cells of:
dd/mm/yyyy;;
it will hide the zero dates.

 

[Peter_SSs]

Not quite sure which cell(s) are Day/Week, but does this do what you want?

=IF(OR(A3="",B14=""),"",SUMIFS(Sheet2!C2:C367,Sheet2!A2:A367,A3,Sheet2!B2:B367,B14))

 

[Luke1690]

Thank you Joe4 i understand why it returned that date now and also a way to fix it to format the cell.

Peter-SSs that worked perfect mate.


Thanks for a speed reply

 

[Joe4]

Thank you Joe4 i understand why it returned that date now and also a way to fix it to format the cell.

Peter-SSs that worked perfect mate.


Thanks for a speed reply

Are you using it in some other sort of calculation or something, where you need it to be physically set to a blank or zero, as opposed to just not being able to see anything in that cell?
Just curious as to why that wouldn't work for you (there may be a legitimate reason...)

 

[Luke1690]

Are you using it in some other sort of calculation or something, where you need it to be physically set to a blank or zero, as opposed to just not being able to see anything in that cell?
Just curious as to why that wouldn't work for you (there may be a legitimate reason...)

Yes all of this data will be collected weekly/monthly and stored in another workbook im going to do this automatically using vba. Then from there its going to be uploaded on to a Tableau database. Not sure if your allowed to say Tableau here hahaha!

i don't want any random data in cells when copying.

i know there are many ways this could of been done, one being yours also vba to ignore that date when copying, i just like everything in formulas or codes.

thanks again.