# Sumifs with date range

#### erniepoe

##### Active Member
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.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
`` =SUMIFS(CMAN!E:E,CMAN!B:B,"*San Diego*",CMAN!A:A,">="&E5,CMAN!A:A,"<"&E6+1)``

#### erniepoe

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

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
4
Views
274
Replies
3
Views
79
Replies
2
Views
114
Replies
5
Views
393
Replies
3
Views
90

1,129,735
Messages
5,638,057
Members
417,001
Latest member
MSteel

### 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?

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