Searching data from month and year
We have a data base with mulitple columns and a dashboard that reports on data with in a month year etc. Ive got the formula returning data my month but with within a specified year. so it reports on anyhing in said month in all the years.
Database looks like this
Im using this formula for the months but just cant work out and to add YEAR to this.
=SUMIF([Quote Date]:[Quote Date], MONTH(@cell ) = 4, [Project Value]:[Project Value])
How can i search the same but just in 2023 2022 etc.
thanks!!
Answers
-
@richard_abra You need multiple criteria so you'll need to use a sum(collect() instead. Try the below and modify as needed. I also added iferror() around the dates to prevent those common errors.
=sum(collect([Project Value]:[Project Value],[Quote Date]:[Quote Date], iferror(MONTH(@cell ),0) = 4,[Quote Date]:[Quote Date], iferror(year(@cell ),0) >=2022,[Quote Date]:[Quote Date],year(@cell )<=2023))
Certified Platinum Partner
-
You can use an AND statement to double up on criteria without having to switch functions to repeat ranges.
=SUMIF([Quote Date]:[Quote Date], AND(IFERROR(MONTH(@cell), 0) = 4, IFERROR(YEAR(@cell), 0) = 2024), [Project Value]:[Project Value])
-
Works Perfect thanks both!!
Next one is how do i add the ability to search by customer wihin year month etc….?
So how many quotes have "Dolphin Solutons LTD" this month or YTD
-
You would need to switch over to a SUMIFS. It does have a slight variation on the syntax (range to sum comes first in this one), so make sure you follow that.
=SUMIFS({Range To Sum}, {1st Criteria Range}, 1st criteria, {2nd Criteria Range}, 2nd criteria, ……………….)
-
what am i doing wrong?
=SUMIFS([Project Value]:[Project Value], [Customer]:[Customer], "dolphin solutions ltd", IFERROR(MONTH(@cell), 0) = 4, [quote date]:[quote date], IFERROR(year(@cell), 0) > = 2024,[Quote Date]:[Quote Date], year(@cell) < = 2024))
-
Can you try this,
=SUMIFS( [Project Value]:[Project Value], Customer:Customer, "dolphin solutions ltd", [Quote Date]:[Quote Date], IFERROR(MONTH(@cell ), 0) = 4, [Quote Date]:[Quote Date], AND( IFERROR(YEAR(@cell ), 0) >= 2024, IFERROR(YEAR(@cell ), 0) <= 2024))
However, this part does not make sense. What do you mean by this?
AND( IFERROR(YEAR(@cell ), 0) >= 2024, IFERROR(YEAR(@cell ), 0) <= 2024))
...
-
Try this:
=SUMIFS([Project Value]:[Project Value], [Customer]:[Customer], "dolphin solutions ltd", [Quote Date]:[Quote Date], AND(IFERROR(MONTH(@cell), 0) = 4, IFERROR(year(@cell), 0) = 2024))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K 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!