SUMIFS/COUNTIFS Function: Total values but filtering out particular Status' AND Dates (Years)

David Slaughter
David Slaughter ✭✭✭
edited 04/27/20 in Formulas and Functions

Hi All,

Firstly, I'll be honest and say I'm unsure whether this is a COUNTIFS or SUMIFS function but leaning towards the SUMIFS function based on my understanding of it.

I'm trying to run a report to show the numner of (Column: Status) Closed Won and Closed Lost opportunities and the (Column name: Value) total value of those opportunities broken down to the relevant years (Column: Date) but the extract from another system lumps all the data from across the years together.

For example, I'd like to show all the total number of 2018 opportunities created (Column: Status) and the total value (Column name: Value) of those opportunities:

Here's my attempt at the query string:

=SUMIFS({PS OPPS Range 1}, "Closed Won"), AND({PS OPPS Range 3}, YEAR(@cell)=2017, value:value)

@Paul Newcome - You managed to save me last time, any chance you can lend a hand here too? Cheeky I know...

Appreciate any help anyone can give.

Many Thanks,



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!