Capture Issues for the Past Month - Formula required.

chris.italiano78351 · 03/30/19

Hi

Can someone please help me again :-(

I am trying to capture the number of issues per brand over the past Month.

For example for Queensland I have the below formula working (Number of issues in total)

=COUNTIF({Dev Drafting Help: Brand}, [Primary Column]17)

I tried to capture for the past month (unsuccessful)

=COUNTIF({Dev Drafting Help: Brand}, [Primary Column]218, {Dev Drafting Help Range 1}, >TODAY(-30))

Screenshot Below is the data i am trying to capture (its in a separate sheet to my metrics). Ideally i would like to capture all issues for the Month of March

Any assistance will be appreciated

Thanks

Nathan Lloyd · 03/30/19

Hi there,

You're very close to having this formula working as expected. You should be able to adjust your formula:

"=COUNTIF({Dev Drafting Help: Brand}, [Primary Column]218, {Dev Drafting Help Range 1}, >TODAY(-30))"

to instead make use of the MONTH() function to highlight the period you're wanting to count for.

It also looks like you're wanting to count based on multiple criteria, so you'll need to use the COUNTIFS function instead. I'm guessing your "{Dev Drafting Help Range 1}" is for the "Date Requested" column.

The updated formula would look something like this:

"=COUNTIFS({Dev Drafting Help: Brand}, [Primary Column]218, {Dev Drafting Help Range 1}, MONTH(@cell) = 3)"

The "3" in this example denotes the number corresponding with the Month out of the year (March in this case). See my screenshot of an example attached. I'm not referencing another sheet, but hopefully this still conveys this well enough.

Example

Hope that helps!

Best,

Nathan

Paul Newcome · 04/01/19

First I am going to suggest a COUNTIFS instead of a COUNTIF. It allows you to build in multiple ranges and corresponding criteria. Then give something like this a go...

=COUNTIFS({Dev Drafting Help: Brand}, [Primary Column]218, {Dev Drafting Help Range 1}, IFERROR(MONTH(@cell), 0) = 3)

This will look at the dates and count only those that are in the month of March. I am assuming that the {Dev Drafting Help Range 1} range is referring to the date column.

Capture Issues for the Past Month - Formula required.

Comments

Categories