Sum by Date Formula - Need Help
=SUMIF({CFL Batch Summaries 2021-Present Flower g}, {CFL Batch Summaries 2021-Present Harvest Date}, YEAR(@cell) = 2021)
I am trying to use this formula to sum the total grams if the harvest date was in the year 2021 but am getting "Invalid Operation".
=SUMIFS({CFL Batch Summaries 2021-Present Flower g}, {CFL Batch Summaries 2021-Present Harvest Date}, YEAR(@cell) = 2021, {CFL Batch Summaries 2021-Present Grade}, =1)
I am also trying the above formula to try and sum for the year and grade but am getting "Invalid Data Type".
Answers
-
Hi @Nate420,
in this formula I see a few issues: =SUMIF({CFL Batch Summaries 2021-Present Flower g}, {CFL Batch Summaries 2021-Present Harvest Date}, YEAR(@cell) = 2021)
I'm gonna assume the following:
{CFL Batch Summaries 2021-Present Flower g} = weight to sum in sheet
{CFL Batch Summaries 2021-Present Harvest Date} = year column in sheet
YEAR(@cell) = 2021 = criterion
If this is correct, the formula should read =
=sumif({CFL Batch Summaries 2021-Present Harvest Date}, 2021, {CFL Batch Summaries 2021-Present Flower g})
For the second formula: =SUMIFS({CFL Batch Summaries 2021-Present Flower g}, {CFL Batch Summaries 2021-Present Harvest Date}, YEAR(@cell) = 2021, {CFL Batch Summaries 2021-Present Grade}, =1)
I think you want this:
=SUMIFS({CFL Batch Summaries 2021-Present Flower g}, {CFL Batch Summaries 2021-Present Harvest Date}, 2021, {CFL Batch Summaries 2021-Present Grade}, 1)
Hope this helps
best,
Brad
www.MVPOPS.com
-
This formula brings back 0.0 for some reason but there should be a large number. Maybe because I have a full date in the column and not just the year?
-
@Nate420 that means the formula is working. Yes, if you don't have a year column than it won't return the proper number. Add a column called year, add the year formula =Year(date field@row), make it a column formula. Use this year column as your criteria range column.
best,
Brad
www.MVPOPS.com
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!