# SumIF month criteria

#### wilkisa

##### Well-known Member
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!

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Place in F2:
Excel Formula:
``=SUMPRODUCT(--(TEXT(DeliveryDate,"mmmm")=F\$1),Vending)``
and copy across

Place in F3:
Excel Formula:
``=SUMPRODUCT(--(TEXT(DeliveryDate,"mmmm")=F\$1),Cafeteria)``
and copy across

Hi Wilkisa,

Here's a different approach.

Wilkisa.xlsx
ABCDEFGHIJKLM
1JanuaryFebruaryMarchAprilMayJuneJulyAugust
214201001000501005050
3Delivery DateVendingCafeteria5231501500751507575
41/3/2022730.00265.00
51/7/2022690.00258.00
62/2/202250.0075.00
72/20/202250.0075.00
83/10/202250.0075.00
93/28/202250.0075.00
105/21/202250.0075.00
116/8/202250.0075.00
126/26/202250.0075.00
137/14/202250.0075.00
148/1/202250.0075.00
Sheet1
Cell Formulas
RangeFormula
G1:M1G1=EOMONTH(F1,0)+1
F2:M2F2=SUMIFS(Vending,DeliveryDate,">="&F\$1,DeliveryDate,"<="&EOMONTH(F\$1,0))
F3:M3F3=SUMIFS(Cafeteria,DeliveryDate,">="&F\$1,DeliveryDate,"<="&EOMONTH(F\$1,0))
Named Ranges
NameRefers ToCells
Cafeteria=Sheet1!\$C\$4:\$C\$500F3:M3
DeliveryDate=Sheet1!\$A\$4:\$A\$500F2:M3
Vending=Sheet1!\$B\$4:\$B\$500F2:M2

Place in F2:
Excel Formula:
``=SUMPRODUCT(--(TEXT(DeliveryDate,"mmmm")=F\$1),Vending)``
and copy across

Place in F3:
Excel Formula:
``=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!

Hi Wilkisa,

Here's a different approach.

Wilkisa.xlsx
ABCDEFGHIJKLM
1JanuaryFebruaryMarchAprilMayJuneJulyAugust
214201001000501005050
3Delivery DateVendingCafeteria5231501500751507575
41/3/2022730.00265.00
51/7/2022690.00258.00
62/2/202250.0075.00
72/20/202250.0075.00
83/10/202250.0075.00
93/28/202250.0075.00
105/21/202250.0075.00
116/8/202250.0075.00
126/26/202250.0075.00
137/14/202250.0075.00
148/1/202250.0075.00
Sheet1
Cell Formulas
RangeFormula
G1:M1G1=EOMONTH(F1,0)+1
F2:M2F2=SUMIFS(Vending,DeliveryDate,">="&F\$1,DeliveryDate,"<="&EOMONTH(F\$1,0))
F3:M3F3=SUMIFS(Cafeteria,DeliveryDate,">="&F\$1,DeliveryDate,"<="&EOMONTH(F\$1,0))
Named Ranges
NameRefers ToCells
Cafeteria=Sheet1!\$C\$4:\$C\$500F3:M3
DeliveryDate=Sheet1!\$A\$4:\$A\$500F2:M3
Vending=Sheet1!\$B\$4:\$B\$500F2:M2
Thank you, Toadstool, for your response. I went with Joe4's solution as it does exactly what I need.

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.

Replies
9
Views
633
Replies
1
Views
961
Replies
4
Views
415
Replies
2
Views
156
Replies
3
Views
759

### Forum statistics

1,203,491
Messages
6,055,727
Members
444,814
Latest member
AutomateDifficulty

### 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?

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