# Searching data from month and year

Options
✭✭✭
edited 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!!

• ✭✭✭✭✭
Options

@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

2023 Partner of the Year

PrimeConsulting.com

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• ✭✭✭
edited 08/01/24
Options

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

• ✭✭✭✭✭
edited 08/01/24
Options

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

...

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!