Data for dashboards

Hi i am looking for some help with some dashboard data.

I would like to display data from a sheet that shows all data captured (YTD) and also current month data. This will be displayed in two metric widgets enabling both the customer and me to make easy comparisons.

The problem i am having is regards to the current month and how to just capture between two date ranges as opposed to the whole sheet

The data is being collected via a form so is auto populated onto a sheet. i have figured how to adjust this manually on a daily basis by extending the data range but wondered if this range could be auto adjusted?.

can i add something to the formula that will still capture new entries but run between certain dates?

Current formula

=COUNTIFS({Pure Gym Issue Type}, "Daytime/ short night shift missed")

I hope that makes sense and would love to see if there is a solution.

Thank you

Dave

Find more posts tagged with

Formulas

Comments

J. Craig Williams

If you are using X-Sheet References, you should not need to worry about extending the range, select the entire column instead of part of it.

To hone in on the date, you need to add the criterion to your COUNTIFS()

Something like this:

=COUNTIFS(...yourformulaasis...,{date range}, IFERROR(MONTH(@cell),0)=MONTH(TODAY()))

will count only the ones for the current month (when viewed) - so viewing today would be only for October.

Craig

dave.mcpherson56751

Hi Craig,

as always you are a star for coming back to me, as you may have gathered i am new and not very good at formulas. i am unsure about the date range and put in the below but get an error? could you please advise when you have a moment?

=COUNTIFS(({Pure Gym Issue Type}, "Daytime/ short night shift missed"){Pure Gym Issue Type},{01/10/18,31/10/18}, IFERROR(MONTH(@cell),0)=MONTH(TODAY()))

Sorry to be a pain

Dave

J. Craig Williams

Without seeing your column structure, I'm just shotgunning here, but this

=COUNTIFS(({Pure Gym Issue Type}, "Daytime/ short night shift missed"){Pure Gym Issue Type},{01/10/18,31/10/18}, IFERROR(MONTH(@cell),0)=MONTH(TODAY()))

is messed up.

=COUNTIFS( range1, criterion1, range2, criterion2, etc...)

Rule #2: All ranges must be same size.

This

{Pure Gym Issue Type}

is a range. Since it is a X-Sheet Ref range, it could be one cell, one selection of continuous cells (1x10, 10,x1, 5x2, etc...), one column, or multiple columns.

This

"Daytime/ short night shift missed"

is what is being checked. One thing that says "looking for X" in each of the cells in the range.

This

...missed"){Pure

is a mistake. It should be a comma as Smartsheet does not know what to do with

{....}){...}

but even changing that won't help because this

{01/10/18,31/10/18}

is a range. I also assume it is incorrectly assumed to be a X-Sheet Reference (it could be, the reference range can be anything and has NO IMPACT on content)

The second criteria's range, which you listed as

{Pure Gym Issue Type},{01/10/18,31/10/18}

is intended in my example to a be a range EXACTLY THE SAME SIZE as {Pure Gym Issue Type}. Preferably from the same source (the X-Sheet Reference source sheet) because of Rule #2

....

Is there a column in the Sheet that {Pure Gym Issue Type} is coming from that is a Date type column? Then you need to bring that over as a NEW X-Sheet Reference.

Maybe you aren't using X-Sheet References at all and just copied the original formula from somewhere?

Either way, SOMEWHERE on the sheet there need to be a range of dates and that what you need to be using.

Lastly, if you ever DO need to use a user generated date (and I don't think you do in this case!) then

DATE(YY, MM, DD)

all arguments are numbers, not text.

=DATE(2018,10,2)

is today.

Craig

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…