plot multivalue drop down menu (non numerical) vs months

Hello,

I have a sheet where we track patient complications

I have columns with the Event Date (when the complication occurred), Date created, and a column with a multiple choice drop down - where as many as applicable can be selected. They are numbers but just words. I need to be able to create a report that shows a bar graph of complications by month.

I was given this formula by SS help desk: =COUNTIF({Date of event}, IFERROR(MONTH(@cell), 0) = [Numerical value]@row)

but it returns =UNPARSEABLE

can someone help me, please?

Find more posts tagged with

Accepted answers

All comments

OshaK

*I meant "They are NOT numbers but just words".

Neil Watson

@OshaK can you share a screen shot of the data? I assume you are pulling the data from the main sheet to another sheet?

OshaK

thank you so much, @Neil Watson , here go, yes, that's what i'm trying to do. I think i figured how to pull data for each complication individually, but i'm hoping there is more robust way to pull them all in one formula? the main sheet has the yellow rows and the another sheet has the formula. TY!

Screen Shot 2023-02-02 at 6.07.03 PM.png

Screen Shot 2023-02-02 at 6.08.41 PM.png

Neil Watson

@OshaK if you are looking to assess a single criteria, then "COUNTIF" works.

However, you are looking for 2 criteria - the month and the complication. So you need to use COUNTIFS.

Unless you are looking for all occurrences of something happening in January (irrespective of the year) then I would separate your helper columns into month and year. And convert these to column formula:

In your metric sheet:

Use COUNTIF for the total cases by month, since the helper column only has months you don't need the MONTH
Use COUNTIF for the complications, alon with HAS function which works with multiple drop downs.
{Month} is a cross-sheet reference to Month (Helper Column)
{Complications} is a cross-sheet reference to Complications

OshaK

Hi @Neil Watson Thank you so much again! I was able to get your formula work for Total cases of the month, but then I'm having problems - I'm using this:

=COUNTIFS({Month}, @cell = $[Numerical value]@row, {Complications}, HAS(@cell, [Readmission within 30 days]$1)) and getting "INCORRECT ARGUMENT' error.

I also tried to add the year column into metric sheet like so: "=COUNTIF({Month}, @cell{Year} = [Total cases by month]@row) and getting "UNPARSEABLE' error. Maybe i'm not referring the master sheet correctly? I clicked on Refer a sheet and selected the same Months column as for the 'Total cases by month' column.

Regardless of it, I tried to create a chart on the dashboard, thinking I could chart Complications (columns) by Month (rows) , but SS is not letting me do that - tells the data can't be charted.

Thank you so much!

master sheet:

Screen Shot 2023-02-03 at 8.26.36 AM.png

metric sheet:

Screen Shot 2023-02-03 at 8.22.10 AM.png

OshaK

sorry @Neil Watson please disregard my comment about the Year column. I just realized that it doesn't make sense since it's a single year, but I will need to chart all the data for multiple years from the different sheets later on. Is it possible? I'm still getting errrors for complications...

Neil Watson

@OshaK, the formula needs to reference the complications in row 1, a formula cannot reference the column name itself. See below.

OshaK

@Neil Watson thank you, I answered to you in the first thread. Many thanks!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.