sum from date not date and time

[slamanager]

Hi,

Trying to sum data in col B only using the criteria of the date in Col A, col A has the date and time format so unsure how to sum. have tried to use the day and month functions but not getting the answer i want.

Thnaks

 

[Andrew Fergus]

Hi

Excel stores dates as whole numbers with the time of day being the value after the decimal place, and given you are only entering a date as the criteria that is why it isn't working.

Try the following array* formula but change the range to suit:

{=SUM(IF(INT(A5:A25)=E5,B5:B25,0))}

E5 contains the cell that has the date you want to sum on.

*array formula : enter the formula above without the curly brackets {}, but before you press Enter, instead press Ctrl then Shift then Enter (holding down all 3 keys at the same time). You will know when you nhave done this correctly when the curly brackets {} automatically appear around the formula.

I trust this helps
Andrew