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

 

[JamesCanale]

try using SUMIFS instead of SUMIF, and do:
,YEAR(date-column-in-Items-Sheet),2020)

 

[rilzniak]

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

Unless I'm misinterpreting, your formula doesn't specify a date (year) range as criteria. Use SUMIFS and include whichever column has the year. So for L19:

SUM(SUMIFS(Items!I$2:I$4995,Items!$E$2:$E$4995,{"Grain - Broiler Crumbles","Grain - Starter Mash"},columnwithyear,"2020"))

 

[jasonb75]

try using SUMIFS instead of SUMIF, and do:
,YEAR(date-column-in-Items-Sheet),2020)

You can't do that with sumifs (or any similar function). If the data contains proper dates then it would need to be in the format of

=SUMIFS(sum range, date range,">="&DATE(2020,1,1), date range, "<="&Date(2020,12,31))

With other criteria added as necessary.

 

[Outdoorsman80]

You can't do that with sumifs (or any similar function). If the data contains proper dates then it would need to be in the format of

=SUMIFS(sum range, date range,">="&DATE(2020,1,1), date range, "<="&Date(2020,12,31))

With other criteria added as necessary.

Hi, the column, D, is formatted in proper dates.

 

[jasonb75]

Try this in L19, then use the fill handle to drag it to M19.

I've assumed that 2020 is in K16 as i looks merged in the mini sheet.

=SUM(SUMIFS(Items!I2:I5000Items!$E2:$E5000,{"Grain - Broiler Crumbles","Grain - Starter Mash"},Items!$D2:$D5000,">="&DATE($K$16,1,1),Items!$D2:$D5000,"<="&Date($K$16,12,31)))

 

[Outdoorsman80]

I'm still having some difficulties.

=SUM(SUMIFS(Items!E2:E5000,Items!J2:J5000, {"Grain - Broiler Crumbles","Grain - Starter Mash"}, Items!D2:D5000,">=1/1/2020", Items!D2:D5000, "<=12/31/2020"))

The worksheet named Items has the Date in the D column. J Column has the price, E column has the Grain type.

Snippet of the Items Sheet:

Finances.xlsx
	D	E	F	G	H	I	J
637	4/1/2020	Grain	50lb - Soy Free	Green Mountain	N/A	1	$ 22.00
638	4/1/2020	Grain - Starter Mash	50lb - Chick Starter	Green Mountain	N/A	1	$ 26.20
639	4/1/2020	Grain - Broiler Crumbles	50lb - Broiler Crumbles	Green Mountain	N/A	1	$ 24.21
662	6/18/2020	Grain - Broiler Crumbles	50lb - Broiler Crumbles	Green Mountain	N/A	1	$ 24.21
Items

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

 

[rilzniak]

I'm still having some difficulties.

=SUM(SUMIFS(Items!E2:E5000,Items!J2:J5000, {"Grain - Broiler Crumbles","Grain - Starter Mash"}, Items!D2:D5000,">=1/1/2020", Items!D2:D5000, "<=12/31/2020"))

The worksheet named Items has the Date in the D column. J Column has the price, E column has the Grain type.

Snippet of the Items Sheet:

Finances.xlsx
	D	E	F	G	H	I	J
637	4/1/2020	Grain	50lb - Soy Free	Green Mountain	N/A	1	$ 22.00
638	4/1/2020	Grain - Starter Mash	50lb - Chick Starter	Green Mountain	N/A	1	$ 26.20
639	4/1/2020	Grain - Broiler Crumbles	50lb - Broiler Crumbles	Green Mountain	N/A	1	$ 24.21
662	6/18/2020	Grain - Broiler Crumbles	50lb - Broiler Crumbles	Green Mountain	N/A	1	$ 24.21
Items

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

L19=

SUM(SUMIFS(Items!I2:I5000,Items!E2:E5000, {"Grain - Broiler Crumbles","Grain - Starter Mash"}, Items!D2:D5000,">="&date(K16,1,1), Items!D2:D5000, "<="&date(K16,12,31)))

M19=

 SUM(SUMIFS(Items!J2:J5000,Items!E2:E5000, {"Grain - Broiler Crumbles","Grain - Starter Mash"}, Items!D2:D5000,">="&date(K16,1,1), Items!D2:D5000, "<="&date(K16,12,31)))

Assuming the 2020 year is in cell K16.

 

[jasonb75]

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

 

[Outdoorsman80]

L19=

SUM(SUMIFS(Items!I2:I5000,Items!E2:E5000, {"Grain - Broiler Crumbles","Grain - Starter Mash"}, Items!D2:D5000,">="&date(K16,1,1), Items!D2:D5000, "<="&date(K16,12,31)))

M19=

 SUM(SUMIFS(Items!J2:J5000,Items!E2:E5000, {"Grain - Broiler Crumbles","Grain - Starter Mash"}, Items!D2:D5000,">="&date(K16,1,1), Items!D2:D5000, "<="&date(K16,12,31)))

Assuming the 2020 year is in cell K16.

Thank you, I think that I was confused by the K16 reference as my mind was in the Items sheet. Those worked perfectly.... thank you again.

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

Thank you as well. As for the dates, the Format Cell is set to Date > Short Date in the Items sheet.