Searching data from month and year

richard_abra · 07/30/24

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!!

Matt Lynn-PCG · 07/30/24

@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))

Paul Newcome · 07/31/24

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])

richard_abra · 08/01/24

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

Paul Newcome · 08/01/24

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, ……………….)

richard_abra · 08/01/24

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))

heyjay · 08/01/24

https://community.smartsheet.com/discussion/comment/447395#Comment_447395

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))

Paul Newcome · 08/01/24

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))

Searching data from month and year

Answers

Help Article Resources

Categories