=SUMIF with date range

Outdoorsman80

New Member
Joined
Oct 4, 2014
Messages
44
Office Version
  1. 365
Platform
  1. Windows
I have a workbook with multiple worksheets. I'm creating a Dashboard that pulls from the other sheets. This dashboard needs to present the amount of grain purchased for 2020 (hard coded year).

This is what I have so far to calculate the type of grain in all years, but I need to filter just one year:
Finances.xlsx
KLM
162020
17SumTotal Cost
18Egg Sales Total
19Meaty Food Total4$ 100.82
Dash
Cell Formulas
RangeFormula
L19L19=SUMIF(Items!E2:E4995,"Grain - Broiler Crumbles",Items!I2:I4995)+SUMIF(Items!E2:E4995,"Grain - Starter Mash",Items!I2:I4995)
M19M19=SUM(SUMIF(Items!E2:E5000,{"Grain - Broiler Crumbles","Grain - Starter Mash"},Items!J2:J5000))

As you can see there, it's working but it's pulling totals from 2020 and 2021. Sum should be 3 and total should be $75.

BTW, XL2BB is excellent... :)
 

rilzniak

Active Member
Joined
Jul 20, 2012
Messages
273
@rilzniak that is the same formula that I suggested earlier.

@Mainer82 your dates are not proper dates, they are text strings. It is unlikely that the formula will recognise them correctly. Ideally, they need to be converted to proper dates, then the formula that I suggested will work as needed.
@jasonb75 I didn't see that but I do notice now that you're missing a comma as you had written it initially (Items!I2:I5000Items!$E2:$E5000).
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,221
Office Version
  1. 365
Platform
  1. Windows
@jasonb75 I didn't see that but I do notice now that you're missing a comma as you had written it initially (Items!I2:I5000Items!$E2:$E5000).
Good spot, I hadn't noticed that. Given that there was no example of the Items sheet at that point I had typed the formula into the post rather than in excel, so the syntax went unchecked.
As for the dates, the Format Cell is set to Date > Short Date in the Items sheet.
They are still text. Formats can only be applied to proper numeric values, a numeric value that is entered in text format will remain text regardless of changes to format. If you try to change it to long date you will see that it doesn't change as expected.

As your dates appear to be in US format and you're looking a the whole year, you should be ok (no guarantees, not all formulas behave in the same way which was why I thought that it was the cause of the problem here).

If you had UK dates entered as text and wanted to summarise data for a single month then it would go horribly wrong.

From the XL2BB capture of the Items sheet, there are 2 things that tell me that your dates are not valid. The first is that they are aligned to the left of the column, dates are normally on the right (although this can be changed in format settings, it is not something that tends to be done, especially with short dates).
The second is that when you hover the cursor over a date in XL2BB, it shows the date serial number (5 digit number, for dates 2020-2021 they will be around 44000), this does not happen on your post. The only exception to this would be a date created from a formula, in which case the formula would be shown instead.
 
Last edited:

Outdoorsman80

New Member
Joined
Oct 4, 2014
Messages
44
Office Version
  1. 365
Platform
  1. Windows
From the XL2BB capture of the Items sheet, there are 2 things that tell me that your dates are not valid. The first is that they are aligned to the left of the column, dates are normally on the right (although this can be changed in format settings, it is not something that tends to be done, especially with short dates).
The second is that when you hover the cursor over a date in XL2BB, it shows the date serial number (5 digit number, for dates 2020-2021 they will be around 44000), this does not happen on your post. The only exception to this would be a date created from a formula, in which case the formula would be shown instead.
I aligned them to the left. If I have something set wrong, I would like to fix it but I'm not following.

date-settings.PNG
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,221
Office Version
  1. 365
Platform
  1. Windows
It's probably not something that you have set wrong, more likely that it has been copied from elsewhere in the wrong format.

There is nothing wrong with left alignment, as I mentioned earlier it is just an unusual setting that I wouldn't expect to see, which is why it caught my attention in the first place.

Try changing one to a different format (anything other than short date or text) does it change in the cell or stay the same?
If it change then it is a good date (valid format), if it doesn't change then it is a text date.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,221
Office Version
  1. 365
Platform
  1. Windows
In that case, they are valid. Seems strange that XL2BB wasn't showing them as such though, maybe the left alignment confused it as much as it confused me :oops:
 

Forum statistics

Threads
1,136,969
Messages
5,678,888
Members
419,787
Latest member
juanam

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