Sumifs with date range

[erniepoe]

Hi all,

I'm using the following sumifs formula. =SUMIFS(CMAN!E:E,CMAN!B:B,"*San Diego*",CMAN!A:A,">="&E5,CMAN!A:A,"<="&E6)

The values in E5 and E6 represent a date range (9/28/20 and 10/04/20, respectively).

The problem is that on the CMAN tab, the dates spit out with not just a date, but a time, and the value in the cell will be something like 10/04/2020 2:45:00 PM.

The time added to the cell is upsetting the formula where it now no longer is inclusive of the date values, so any values from 10/04/20 are not being included in the formula. Editing the cells on the CMAN tab is unfortunately not an option, so I need to refine the above formula to take this into account

Any advice is much appreciated.

 

[Fluff]

How about

 =SUMIFS(CMAN!E:E,CMAN!B:B,"*San Diego*",CMAN!A:A,">="&E5,CMAN!A:A,"<"&E6+1)

 

[erniepoe]

Ah, success! Such a simple solution I feel dumb for not figuring it out. Thank you, Fluff. Cheers.

 

[Fluff]

You're welcome & thanks for the feedback.