SUMIFS a product between dates

[bootdat]

POSv3-General.xlsm
	AC	AD	AE	AF	AG	AH
2
3	February
4	01 February 2021			Sunday, 28 February 2021
5
6	Advertisement (Social Media)	0
7	Aluminum Foil	0
8	Baking Paper	0
9	Bin Liner	0
10	Call Credit	0
11	Cash Gifts	0
12	Charcoal	0
Report

Cell Formulas
Range	Formula
AC3	AC3	=TODAY()
AC4	AC4	=EOMONTH(AC3,-1)+1
AF4	AF4	=EOMONTH(AC3,0)
AD6:AD12	AD6	=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
	H	I	J	K
2	Date	Source	Item	Cost
3	31-Jan-21	Cash	Advertisement (Social Media)	12.00
4	01-Feb-21	MoMo	Aluminum Foil	50.00
5	02-Feb-21	Bank	Baking Paper	4.00
6	03-Feb-21	Cash	Bin Liner	6.00
7	04-Feb-21	MoMo	Call Credit	45.00
8	05-Feb-21	Bank	Cash Gifts	35.00
9	06-Feb-21	Cash	Charcoal	5.00
10	07-Feb-21	MoMo	Cling Film	8.00
11	08-Feb-21	Bank	Disposable Cups	100.00
12	09-Feb-21	Cash	Disposable Plates	82.00
13	10-Feb-21	MoMo	Disposable Spoons	4.00
14	11-Feb-21	Bank	Electricity	81.00
Expenses

Cells with Data Validation
Cell	Allow	Criteria
I3:I42	List	='All Items'!$L$28:$L$30
J3:J42	List	='All Items'!$P$2:$P$65

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

 

[Toadstool]

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]

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:

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

 

[bootdat]

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:

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

WORKED!!

Thanks

 

[bootdat]

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:

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

WORKED!!

Thanks