Dashboard Reference Data

Ken Hoffman

Hi,

I am trying to create a Dashboard reference sheet. I want to display to the client the number of quotes they requested and the number of quotes we presented to them on time. Quotes have a time line based on $ value. I don't think I have the right formula to capture the data accurately. What I need is if the quote due date is on or before the quote submitted date the quote is considered "on time" I need to provide the client data that we are providing "on time" requests.

Right now I am just referencing the number of quotes/month by how many we submitted with is not an accurate representation.

Thanks in advance.

Paul Newcome

Your syntax is still off. You have an extra closing parenthesis that needs removed after "Yes" and you are missing the second range (the date range).

Paul Newcome

You would want to insert a helper column on the source sheet and use somethign like this in it:

=IF([Date Quote Submitted]@row > [Quote Due]@row, "Late")

Then in your metrics sheet you can use a COUNTIFS to count how many rows are "Late" (or blank if you wanted on time) based on date (if you wanted a monthly breakdown) to get your count.

Ken Hoffman

Thanks Paul, I added a column for "Quote on Time" with the following formula =IF([Date Quote Submitted]@row <= [Quote Due]@row, "Yes"). One issue is that if they are blank for "Date quote Submitted" it also says yes.

What would I do keep the quote on time blank if the summited date is blank and if the quote is late put No instead of having it blank? I'm sure I will have a follow up on my reference sheet :)

Paul Newcome

Try something like this:

=IF([Date Quote Submitted]@row <> "", IF([Date Quote Submitted]@row <= [Quote Due]@row, "Yes", "No"), IF([Quote Due]@row < TODAY(), "No"))

Ken Hoffman

Thanks Paul that is closer to what I am looking for. If I have a Blank in any of the columns how do I leave the "quote on time" Column blank. ATM it come up No.

Again thank you for all your help.

Paul Newcome

Lets try this:

=IF([Quote Due]@row <> "", IF([Date Quote Submitted]@row <> "", IF([Date Quote Submitted]@row <= [Quote Due]@row, "Yes", "No"), IF([Quote Due]@row < TODAY(), "No")))

Ken Hoffman

Hi Paul, That absolutely fixed it! So now back to my original, on my Data sheet I would us a COUNTIFS for yes. I don't think I'm doing it right. but will it also count per month?

Paul Newcome

Your syntax is off.

=COUNTIFS(range1, criteria1, range2, criteria2, .................................)

And it will not do a monthly breakdown by default. You would need to include the date column as a range and a criteria for the date range of...

IFERROR(MONTH(@cell), 0) = #)

and change the # to whichever month number you want to use.

Ken Hoffman

Hi Paul,

Thanks for the reply. I am getting a #unparseable for the following. What am I missing?

=COUNTIFS({Projects Range 1},"Yes"),IFERROR(Month(@cell),0)=1

Paul Newcome

Your syntax is still off. You have an extra closing parenthesis that needs removed after "Yes" and you are missing the second range (the date range).

Ken Hoffman

Thank you Paul for all you help. That got it. I'll get these formulas one of these days :)

Ken Hoffman

Paul Newcome

Happy to help. 👍️