SumIF month criteria

[wilkisa]

I know this is a simple question but I'm feeling extra stupid today and cannot make this work! My data is as follows: A4:A500 named range as "DeliveryDate", B4:B500 named range as "Vending", C4:C500 named range as "Cafeteria". Invoices are entered beginning in A4 as date delivered, cost of vending products, cost of cafeteria products. We usually enter the invoices the day after they are received so they are generally in date order. In Cell F1 we have entered "January", G1 is "February" and so on across. In F2, I need a SumIF formula to sum all "Vending" invoices whose month in DeliveryDate matches F1. I'll then need the same formula in F3 for "Cafeteria". I've tried multiple different SumIF formulas and I'm just not getting it! I keep getting errors. =SumIF(Text(DeliveryDate,"mmmm"),F1,Vending)

A quick sample of data:

DeliveryDate Vending Cafeteria
1/3/2022 730.00 265.00
1/7/2022 690.00 258.00

I should have a Vending sum of 1420.00 and a Cafeteria sum of 523.00.

If someone can set me straight, I'd really appreciate it. Thank you!

 

[Joe4]

Place in F2:

=SUMPRODUCT(--(TEXT(DeliveryDate,"mmmm")=F$1),Vending)

and copy across

Place in F3:

=SUMPRODUCT(--(TEXT(DeliveryDate,"mmmm")=F$1),Cafeteria)

and copy across

 

[Toadstool]

Hi Wilkisa,

Here's a different approach.

Wilkisa.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1						January	February	March	April	May	June	July	August
2						1420	100	100	0	50	100	50	50
3	Delivery Date	Vending	Cafeteria			523	150	150	0	75	150	75	75
4	1/3/2022	730.00	265.00
5	1/7/2022	690.00	258.00
6	2/2/2022	50.00	75.00
7	2/20/2022	50.00	75.00
8	3/10/2022	50.00	75.00
9	3/28/2022	50.00	75.00
10	5/21/2022	50.00	75.00
11	6/8/2022	50.00	75.00
12	6/26/2022	50.00	75.00
13	7/14/2022	50.00	75.00
14	8/1/2022	50.00	75.00
Sheet1

Cell Formulas
Range	Formula
G1:M1	G1	=EOMONTH(F1,0)+1
F2:M2	F2	=SUMIFS(Vending,DeliveryDate,">="&F$1,DeliveryDate,"<="&EOMONTH(F$1,0))
F3:M3	F3	=SUMIFS(Cafeteria,DeliveryDate,">="&F$1,DeliveryDate,"<="&EOMONTH(F$1,0))

Named Ranges
Name	Refers To	Cells
Cafeteria	=Sheet1!$C$4:$C$500	F3:M3
DeliveryDate	=Sheet1!$A$4:$A$500	F2:M3
Vending	=Sheet1!$B$4:$B$500	F2:M2

 

[wilkisa]

Place in F2:

=SUMPRODUCT(--(TEXT(DeliveryDate,"mmmm")=F$1),Vending)

and copy across

Place in F3:

=SUMPRODUCT(--(TEXT(DeliveryDate,"mmmm")=F$1),Cafeteria)

and copy across

Thank you so much! I tried the SumProduct before I tried SumIF and just couldn't wrap my head around either one today. This works perfectly!

 

[wilkisa]

Hi Wilkisa,

Here's a different approach.

Wilkisa.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1						January	February	March	April	May	June	July	August
2						1420	100	100	0	50	100	50	50
3	Delivery Date	Vending	Cafeteria			523	150	150	0	75	150	75	75
4	1/3/2022	730.00	265.00
5	1/7/2022	690.00	258.00
6	2/2/2022	50.00	75.00
7	2/20/2022	50.00	75.00
8	3/10/2022	50.00	75.00
9	3/28/2022	50.00	75.00
10	5/21/2022	50.00	75.00
11	6/8/2022	50.00	75.00
12	6/26/2022	50.00	75.00
13	7/14/2022	50.00	75.00
14	8/1/2022	50.00	75.00
Sheet1

Cell Formulas
Range	Formula
G1:M1	G1	=EOMONTH(F1,0)+1
F2:M2	F2	=SUMIFS(Vending,DeliveryDate,">="&F$1,DeliveryDate,"<="&EOMONTH(F$1,0))
F3:M3	F3	=SUMIFS(Cafeteria,DeliveryDate,">="&F$1,DeliveryDate,"<="&EOMONTH(F$1,0))

Named Ranges
Name	Refers To	Cells
Cafeteria	=Sheet1!$C$4:$C$500	F3:M3
DeliveryDate	=Sheet1!$A$4:$A$500	F2:M3
Vending	=Sheet1!$B$4:$B$500	F2:M2

Thank you, Toadstool, for your response. I went with Joe4's solution as it does exactly what I need.

 

[Joe4]

Thank you so much! I tried the SumProduct before I tried SumIF and just couldn't wrap my head around either one today. This works perfectly!

You are welcome.
Glad I was able to help.

Note that when marking posts as the solution, you want to mark the post that originally contains the solution (not your post acknowledging that a previous response worked).
I have updated it for you.