=SUMIF with date range

[Outdoorsman80]

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
	K	L	M
16	2020
17		Sum	Total Cost
18	Egg Sales Total
19	Meaty Food Total	4	$ 100.82
Dash

Cell Formulas
Range	Formula
L19	L19	=SUMIF(Items!E2:E4995,"Grain - Broiler Crumbles",Items!I2:I4995)+SUMIF(Items!E2:E4995,"Grain - Starter Mash",Items!I2:I4995)
M19	M19	=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]

@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).

 

[jasonb75]

@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.

 

[Outdoorsman80]

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.

 

[jasonb75]

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.

 

[Outdoorsman80]

It changes. From Date to Number it changes.

 

[jasonb75]

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