Dates in SUMIFS formula

samerf86

New Member
Joined
Aug 13, 2020
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
Hi,
Exc-1.jpg

This is a sample of the data entry sheet.
In another sheet, I am writing a SUMIFS formula to calculate the quantity based on the item, its phase and date.
The problem occurs when I am trying to calculate the quantities of previous dates: ex: SUMIFS(Qtyrange,Itemrange,X,Phaserange,Y,Daterange,<Sep-20)
What should I write in the Date Criteria for the formula to take into consideration all the months previous to Sep-20?

Appreciate your help guys.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,969
Office Version
  1. 365
Platform
  1. Windows
The bit that you've highlighted should be enclosed in double quotes, "<Sep-20"

That should work as long as all of your dates are valid.
 

samerf86

New Member
Joined
Aug 13, 2020
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
No it doesn't work if I add the double quotes.
Any other suggestions?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,969
Office Version
  1. 365
Platform
  1. Windows
Works fine for me that way, there are a number of reasons why it could fail.

Does the formula give a result without the date range and date criteria?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,957
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Note that if your entries are really entered as valid dates, they will have a year, month, and day compenent (all 3).
Even if you format it to not show the day, it is still in there, and Excel will use it in the calculations.

I would recommend temporarily changing the data format on the date data for the time being, so you can see what really is in there, and adjust your formula accordingly.
 
Last edited:

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,969
Office Version
  1. 365
Platform
  1. Windows
The point of testing without the date range is to check if they are valid dates, Joe. With no day entered, a valid date defaults to the 1st day of the month (edit: this also applies to the criteria when entered as I suggested), given that the criteria is for previous months only, the formula will work with valid dates regardless of day.

Given that everything is centre aligned in the screen capture, my usual tactic of best guessing validity based on left or right alignment goes out of the window.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,957
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The point of testing without the date range is to check if they are valid dates, Joe. With no day entered, a valid date defaults to the 1st day of the month, given that the criteria is for previous months only, the formula will work with valid dates regardless of day.
I am not sure if you are using a US version of Excel, or European version, but if I enter "Sep-20" in any cell, Excel assumes I am entering month and day, and it defaults to the current year. So the date is NOT defaulted to September 1, 2020, but rather Septemeber 20, 2020.

If it is existing data, the value being shown to us in the Date column is dependent upon the format on the cell.
If it is "mmm-dd", it showing the month and day.
If it is "mmm-yy", it is showing the month and year.
Based on the small sample size shown, it is not obvious which one we are looking at. If they tell us how that cell is formatted, then we will know that bit, but there will still be one unknown (year or day).

It is important to understand exactly what we are working with before coming up with a solution (I don't really like to make assumptions on those sort of things).

samerf86,
Can you either tell us exactly what is in those date cells, when you select the cell and look in the formula bar?
Or temporarily change the format of the cell to show month, day, and year?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,969
Office Version
  1. 365
Platform
  1. Windows
I am not sure if you are using a US version of Excel, or European version
UK regional formats, Joe. Something that makes a big difference.
It is important to understand exactly what we are working with before coming up with a solution (I don't really like to make assumptions on those sort of things).
Wasn't so much an assumption, just failure to make allowance for ambiguous US format dates. In the UK, the examples could only be mmm-yy.

I can't see anything that identifies the OP's location, but assuming US formatting, that could explain the problems.
 

samerf86

New Member
Joined
Aug 13, 2020
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
Hi guys,

The date format in the Data entry sheet is (month/year); and you were right, Excel considered the date as 20-Aug-20.
In any case, the formula works if I write it like this:
Exc-1.jpg

However, when I try to link it to the Cell J1 instead of writing 20-Oct-20, it stops working.
How do I get around that?

Appreciate your help.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,969
Office Version
  1. 365
Platform
  1. Windows
It should work if you write it as

"<"&$J$1

Although, given the problems already encountered, anything could happen :eek:

One other thought, what happens if you enter it as Oct-2020? In theory, that should eliminate the need for excel to guess if it is mmm-dd or mmm-yy, but I don't know if that will be valid with US regional settings.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,040
Messages
5,599,456
Members
414,312
Latest member
mikefire911

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?

Disable AdBlock

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
Top