SUMIFS a product between dates

bootdat

New Member
Joined
Nov 22, 2018
Messages
44
POSv3-General.xlsm
ACADAEAFAGAH
2
3February
401 February 2021Sunday, 28 February 2021
5
6Advertisement (Social Media)0
7Aluminum Foil0
8Baking Paper0
9Bin Liner0
10Call Credit0
11Cash Gifts0
12Charcoal0
Report
Cell Formulas
RangeFormula
AC3AC3=TODAY()
AC4AC4=EOMONTH(AC3,-1)+1
AF4AF4=EOMONTH(AC3,0)
AD6:AD12AD6=SUMIFS(Table18[Cost],Table18[Date],">=”&AC4",Table18[Date],"”<=”&EOMONTH(AF4,0)",Table18[Item],Report!AC6)


I want to sum each product total value between first day of the month and last

from this table:

POSv3-General.xlsm
HIJK
2DateSourceItemCost
331-Jan-21CashAdvertisement (Social Media)12.00
401-Feb-21MoMoAluminum Foil50.00
502-Feb-21BankBaking Paper4.00
603-Feb-21CashBin Liner6.00
704-Feb-21MoMoCall Credit45.00
805-Feb-21BankCash Gifts35.00
906-Feb-21CashCharcoal5.00
1007-Feb-21MoMoCling Film8.00
1108-Feb-21BankDisposable Cups100.00
1209-Feb-21CashDisposable Plates82.00
1310-Feb-21MoMoDisposable Spoons4.00
1411-Feb-21BankElectricity81.00
Expenses
Cells with Data Validation
CellAllowCriteria
I3:I42List='All Items'!$L$28:$L$30
J3:J42List='All Items'!$P$2:$P$65


The formula I'm using doesn't seem to work, i need help.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,787
Office Version
  1. 2016
Platform
  1. Windows
Hi BootDat,

Some of your double quotes aren't double quotes (decimal 148 instead of 34) and I'd lock the date cells so you can copy the formula down.

Try this in AD6 then copy it down:
=SUMIFS(Table18[Cost],Table18[Date],">="&$AC$4,Table18[Date],"<="&EOMONTH($AF$4,0),Table18[Item],Report!AC6)
 
Solution

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Didn't recreate your data and table to test, but you had erroneous quote marks, and unnecessary function for AF4 in the formula (since AF4 is already End of the Month) try:

Excel Formula:
=SUMIFS(Table18[Cost],Table18[Date],">="&AC4,Table18[Date],"<="&AF4,Table18[Item],Report!AC6)
 

bootdat

New Member
Joined
Nov 22, 2018
Messages
44
Hi,

Didn't recreate your data and table to test, but you had erroneous quote marks, and unnecessary function for AF4 in the formula (since AF4 is already End of the Month) try:

Excel Formula:
=SUMIFS(Table18[Cost],Table18[Date],">="&AC4,Table18[Date],"<="&AF4,Table18[Item],Report!AC6)
WORKED!!

Thanks
 

bootdat

New Member
Joined
Nov 22, 2018
Messages
44
Hi,

Didn't recreate your data and table to test, but you had erroneous quote marks, and unnecessary function for AF4 in the formula (since AF4 is already End of the Month) try:

Excel Formula:
=SUMIFS(Table18[Cost],Table18[Date],">="&AC4,Table18[Date],"<="&AF4,Table18[Item],Report!AC6)
WORKED!!

Thanks
 

Forum statistics

Threads
1,136,288
Messages
5,674,867
Members
419,530
Latest member
undisclosed

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