Creating Formulas for Metrics

jimhmeyers

Hi Everyone! I am looking to get some metrics for a dashboard. I am trying to make a formula that will give me the amount of entries I have on my grid. based on the Line of Business and the Quarter.

Both the Quarter and LOB columns are drop downs. Quarter has Q1, Q2, Q3 and Q4 while the LOB has Food & Beverage/Dining, Attractions, Merchandise, and Entertainment (currently Food and Beverage/Dining is the only entry type I have in my grid.)

I am trying to get the amount of entries under the Food & Beverage that are also Q1.

I have tried count and sum formulas but am getting the unparesable errors every time.

Thank you for any and all help 😁

Nick Korna

Hi @jimhmeyers,

You would probably want to use COUNTIFS. For example:

=COUNTIFS(Quarter:Quarter,"Q1",[LOB (Line of Business)]:[LOB (Line of Business)],"Food & Beverage/Dining")

This would should count the displayed lines.

If the dropdown(s) would allow multiple selections, the formula would change slightly - do you have anything along these lines or is each row only ever assigned 1 LOB?

In order to speed things up, you could use a separate sheet for the sums to save a bit of time for needing to reproduce/alter the formulas.

Hope this helps, but if you've any problems/questions then just post! 🙂

jimhmeyers

Hi @Nick Korna! Thank you for assisting. The drop downs do not allow for multiple selections. The original formula I have been trying is:

=COUNTIFS({LOB}, HAS (@cell, "Food & Beverage"), {Quarter}, HAS (@cell, "Q1"))

The {LOB} and {Quarter} are references to my sheet. Do you see what I am missing or am I going about it totally incorrectly?

Nick Korna

Your formula is fine, there is just a small error - your LOB criteria is missing a bit of text from the dataset (bolded to make it easy to see):

=COUNTIFS({LOB}, HAS(@cell, "Food & Beverage/Dining"), {Quarter}, HAS(@cell, "Q1"))

If you're only using single options you can ditch the HAS to make it shorter:

=COUNTIFS({LOB}, "Food & Beverage/Dining", {Quarter}, "Q1")

My other suggestion would be to add a Year column into your dataset so you can use the cross sheet over multiple years. In order to save time on formula typing you can also rig up something along the lines of this:

Where the formula is:

=COUNTIFS({LOB}, [LOB (Line of Business)]@row, {Quarter}, Quarter@row, {Year}, Year@row)

This lets you just add quarters/years/LOBs as needed and the formula (once converted to column) will automatically grab the data for you without needing to change the criteria.

Let me know if you still have any issues!

jimhmeyers

Good Morning @Nick Korna thank you thank you thank you!

For some reason =COUNTIFS({LOB}, HAS(@cell, "Food & Beverage/Dining"), {Quarter}, HAS(@cell, "Q1")) did not work, but the shorter formula =COUNTIFS({LOB}, "Food & Beverage/Dining", {Quarter}, "Q1") did work!

I have been thinking about doing a yearly column, but not sure how we will move forward with the information.

Nick Korna

An alternative to adding another column would be to amend the data in your Quarter column - for example, amend "Q1" to "Q1 2024" or "Q1 24" for the first quarter of 2024.

If the Quarter column is being fed by data with dates, this can be converted to quarter/quarter with year as well.