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

David Slaughter

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,

David

Paul Newcome

You are going to end up using a few different functions for this including both the SUMIFS and COUNTIFS functions (if I am reading your screenshot correctly).

To get the total number of Closed Won statuses in 2018, it would look something along the lines of...

=COUNTIFS({Other Sheet Status Column}, "Closed Won", {Other Sheet Date Column}, IFERROR(YEAR(@cell), 0) = 2018)

To get the total value:

=SUMIFS({Other Sheet Value Column}, {Other Sheet Status Column}, "Closed Won", {Other Sheet Date Column}, IFERROR(YEAR(@cell), 0) = 2018)