SUMIFS with year not working

bootdat

New Member
Joined
Nov 22, 2018
Messages
44
The formula im using doesnt seem to work and i cant figure out why..

POSv3-General.xlsm
AJAKALAMANAO
3SalesDirect ExpensesIn-direct Expenses
4YearAmountAmountAmount
52021GH₵0.00GH₵0.00GH₵0.00
62022GH₵0.00GH₵0.00GH₵0.00
72023GH₵0.00GH₵0.00GH₵0.00
82024GH₵0.00GH₵0.00GH₵0.00
92025GH₵0.00GH₵0.00GH₵0.00
Report
Cell Formulas
RangeFormula
AK5:AK9AK5=SUMIFS(Table5[Sum],Table5[Date],”>=”&DATE(AJ5,1,1),Table5[Date],”<=”&DATE(AJ5,12,31))
AM5:AM9AM5=SUMIFS(Table14[Cost],Table14[Date],”>=”&DATE(AJ5,1,1),Table14[Date],”<=”&DATE(AJ5,12,31))
AO5:AO9AO5=SUMIFS(Table18[Cost],Table18[Date],”>=”&DATE(AJ5,1,1),Table18[Date],”<=”&DATE(AJ5,12,31))



SALES TABLE
POSv3-General.xlsm
ABCDEFGH
1Invoice NumberPayMethodDateQtyItem DescriptionPriceSumCus Name
2xxx
3MAR2100007MoMo2 Mar 202117 Inch Whipped Cream Cake [Sq]280230Kendrick
4MAR2100007MoMo16 Mar 20215Apple Pie525Kendrick
5MAR2100007MoMo21 Mar 20211Bread Rolls [Beef]510Kendrick
6MAR2100007MoMo10 Mar 20232Pound Cake100200Kendrick
7MAR2100008Cash22 Mar 202418 Inch Fondant Cake [Rd]350350dav
Invoice Data



DIRECT EXPENSES TABLE
POSv3-General.xlsm
ABCDE
2DateSourceItem NameQtyCost
3
422-Mar-21MoMoApple52200.00
522-Mar-22CashWatermelon10400.00
6
7
8
9
Expenses
Cells with Data Validation
CellAllowCriteria
B3:B158List='All Items'!$L$28:$L$30
C3:C158List='All Items'!$N$2:$N$168


IN-DIRECT EXPENSES TABLE
POSv3-General.xlsm
HIJK
2DateSourceItemCost
3
422-Mar-21CashAdvertisement (Social Media)100.00
523-Mar-21MoMoSalary200.00
6
Expenses
Cells with Data Validation
CellAllowCriteria
I3:I42List='All Items'!$L$28:$L$30
J3:J42List='All Items'!$P$2:$P$65


May i know what seems to be the problem and how to fix it
Thanks
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,611
Office Version
  1. 365
Platform
  1. Windows
You have the wrong type of quotes in your formulae, they should be like
Excel Formula:
=SUMIFS(Table5[Sum],Table5[Date],">="&DATE(AJ5,1,1),Table5[Date],"<="&DATE(AJ5,12,31))
 
Solution

bootdat

New Member
Joined
Nov 22, 2018
Messages
44
You have the wrong type of quotes in your formulae, they should be like
Excel Formula:
=SUMIFS(Table5[Sum],Table5[Date],">="&DATE(AJ5,1,1),Table5[Date],"<="&DATE(AJ5,12,31))
Thanks
I always seem to have challenges with the quotes.

Thanks again
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,611
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,666
Messages
5,637,649
Members
416,979
Latest member
CapeCon

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