Updating a current formula for a report in a widget to include dates

Jayne O'Leary

I have an issue where the current formula feeds into the report correctly and displays on the dashboard widget however, I have now been asked to change the formula to be date specific. I need to have this display by our fiscal year (July 1, 2018-June 30, 2019). Pro-desk gave me the formula to copy and paste but the instructions on how to adjust the ranges and dates are vague and I need a step by step. Here is are the instructions/can anyone help me? I have been waiting all day for a response from the help center to my voicemail and email.... 

=COUNTIFS({Email, Registrations and Webpages Range 1},

Status@row, {Email, Registrations and Webpages Date Range},

@cell >= DATE(2019, 6, 1), {Email, Registrations and Webpages

Date Range}, @cell < DATE(2019, 8, 1))

      To ensure that this works please check that the ranges being

       referenced are set to include the whole column. To do this please left

       click on the cell with the formula and then in the blue pop up preview

       window please left click on each Edit Reference option before

       selecting the whole column.

       Additionally please change the entries in the DATE section to match

       the dates that you would like to include. Please note that this in the

       year, month, day format.

For the Job Types Count column a new range to target the Job Type

would have to be created and then this would have to be adjusted in

the same way as the other formula but if would likely look similar to

this:

=COUNTIFS({Email, Registrations and Webpages Job Title Range},

[Job Title]@row, {Email, Registrations and Webpages Date Range},

@cell >= DATE(2019, 6, 1), {Email, Registrations and Webpages

Date Range}, @cell < DATE(2019, 8, 1))

Paul Newcome

I assume you are familiar with using cross sheet references. If that is the case, just make sure that your range references are the entire column in the data sheet.

 

For the date changes...

 

The first date would be changed from 

 

DATE(2019, 6, 1)

To

DATE(2018, 7, 1)

 

and the second from 

 

DATE(2019, 8, 1)

to

DATE(2019, 7, 1)

Jayne O'Leary

Hi Paul,

thanks for this. I am unfortunately not familiar with cross sheet references and used my pro-desk time to learn how. Unfortunately, the pro-desk rep wasn't sure how to set this up. The date changes I tried. Its the range references for the entire column I am not familiar with. I tried highlighting the entire column and then tried selecting from the first date to the last date within the column. Both scenarios give me #invalidref and #unparesable.

can you offer a little more insight for the range reference. I appreciate any help.

thanks Jayne

Paul Newcome

Ok. Start typing your formula...

 

=COUNTIFS(

.

A box will pop up underneath of where you are typing that has some formula help points in it. You will see a link in the top left corner of this box that says "Reference Another Sheet". Click on it. A new window will pop up. Select the other sheet from the list on the left (give it a few seconds to load). When it comes up, you will want to click on the corresponding column header to select the entire column (it appears as if this first one would be the column housing the Job Title).

At this point I recommend renaming your ranges. The range names default to "Sheet Name Range #". I personally like to change mine to "Sheet Name Column Name" (if the previously posted references are indicative of the correct names, this range would be "Email, Registrations and Webpages Job Title" with the sheet name being the bold portion and the column name being the underlined portion) just to keep things organized.

 

Once you have selected your range (and optionally renamed it), click on the blue box in the bottom right corner that says "Insert Reference".

.

After clicking on that, you will be taken back to the sheet you were entering the formula on, but it will now have the cross sheet reference.

 

=COUNTIFS({Email, Registrations and Web Pages Job Title}

.

Now we need to enter the criteria we are looking for within that range, which would be whatever title is in the Job Title column of the current sheet on whatever row the formula is on, so we type in a comma and then type in the cell reference (you can leave @row as is - there is no need to use a row number unless you are referencing a row other than the one that the formula is on).

 

=COUNTIFS({Email, Registrations and Web Pages Job Title}, [Job Title]@row

.

Now we type in another comma and enter the next range we want to look at using the previously listed steps for cross sheet referencing.

=COUNTIFS({Email, Registrations and Web Pages Job Title}, [Job Title]@row, {Email, Registrations and Web Pages Date}

.

Since we are looking at the same range for two sets of criteria (greater than one date and less than another date), we can actually incorporate an AND function to keep us from having to retype the range more than we really need to. The AND function requires a logical statement, so we will leverage the @cell function to tell the formula to look at each cell within the range individually instead of the entire range as a whole. Since we are just looking at the Job Title and Date for your formula, we can go ahead and close out the COUNTIFS after this one as well...

 

=COUNTIFS({Email, Registrations and Web Pages Job Title}, [Job Title]@row, {Email, Registrations and Web Pages Job Title}, AND(@cell >= DATE(2018, 07, 01), @cell < DATE(2019, 07, 01))

.

And there you have it... Cross sheet referencing and efficiency tips all rolled into one quick breakdown.

 

Let me know if you need further clarification or if something still isn't working for you.

Jayne O'Leary

Hi Paul, 

I am now receiving a #PARESEABLE error on this after going through the step by step

=COUNTIFS({Email, Registrations and Webpages Job Status}, (Status)@row, {Email, Registrations and Webpages Date Range}, AND(@ cell >= DATE(2018,07,01), @cell < DATE(2019,07,01)).

Sorry I must be missing something. I also tried to apply this to the group for job title and the date range and I get a #INCORRECT ARGUMENT SET.

I hope to get on a call with support soon.

thanks

Paul Newcome

Remove the parenthesis from around Status. Assuming your ranges are set properly, this should clear things up for you.