SUMIFS a product between dates

bootdat

New Member
POSv3-General.xlsm
2
3February
401 February 2021Sunday, 28 February 2021
5
7Aluminum Foil0
8Baking Paper0
9Bin Liner0
10Call Credit0
12Charcoal0
Report
Cell Formulas
RangeFormula
AC3AC3=TODAY()
AC4AC4=EOMONTH(AC3,-1)+1
AF4AF4=EOMONTH(AC3,0)

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

from this table:

POSv3-General.xlsm
HIJK
2DateSourceItemCost
401-Feb-21MoMoAluminum Foil50.00
502-Feb-21BankBaking Paper4.00
603-Feb-21CashBin Liner6.00
704-Feb-21MoMoCall Credit45.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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Well-known Member
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)`

jtakw

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

Replies
3
Views
74
Replies
6
Views
71
Replies
0
Views
31
Replies
1
Views
212
Replies
3
Views
129

1,132,640
Messages
5,654,537
Members
418,138
Latest member
agnesegras

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.

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

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