Cross Sheet Countifs multiple columns

I’ve built out an Error Tracking Mastersheet and I’m now trying to roll the metrics up but I’m struggling a bit. There are 4 types of errors that can be made (DE, CE, LE, EE). There are close to 20 people, but for the example below I only show 3. Each person is their own Drop down column with the same 4 error types. Each line will only ever have One Person with their Single Error (multiples not selectable). These are coming in on a Form. You can see in the example that the Date April 2 is selected twice, but Person 1 and Person 2 each have a DE on their own lines. For the life of me I’m struggling to roll up a Countifs of something like all DEs in the month of April.

Ultimately I am looking to pull into a separate Metric sheet and create a Stacked Bar graph of the # of errors per type month. I can envision setting up the data, I just can’t figure out how to setup the formula to pull the Countifs across sheets.

Find more posts tagged with

Formulas

graphs

Accepted answers

David Tutwiler

Because you only have 1 person submitting at any one time, I think you can have a pretty simple solution if you just add a helper column in your sheet that the form dumps data to. If you add a helper column, we'll call it Person Rollup, and add a formula that brings in all of the entries for each person, then you should be left with a column that always has your error code no matter who submitted it. You can write the formula once and make it a column formula so every cell will have the helper automatically calculated The formula for that column should look like:

=JOIN([Person 1]@row:[Person 20]@row, "")

From there, you should be able to just look for the date to be in the correct month and the error code to be the one you're looking for. The formula would look something like:

=COUNTIFS(Date:Date, IFERROR(MONTH(@cell), 0) = 4, [Person Rollup]:[Person Rollup], "DE")

*Note that the ranges will be dependent on if you're on the same sheet or doing cross-sheet references, but this should give you a template on how to write it.

All comments

David Tutwiler

=JOIN([Person 1]@row:[Person 20]@row, "")

From there, you should be able to just look for the date to be in the correct month and the error code to be the one you're looking for. The formula would look something like:

=COUNTIFS(Date:Date, IFERROR(MONTH(@cell), 0) = 4, [Person Rollup]:[Person Rollup], "DE")

*Note that the ranges will be dependent on if you're on the same sheet or doing cross-sheet references, but this should give you a template on how to write it.

jedelman

Thank you @David Tejml. That worked perfectly and taught me a trick I haven't yet seen before but makes complete sense. I love me a helper column but I've never thought to use one like that in a sheet like this.

All the best!

-Jake

David Tutwiler

Great to hear. Glad it is working for you.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.