Column formulas in cross sheet calculations

i am trying to count how many coils have at least 1 piece of missing test data. The report I pull will show all the information and the missing test data by type. I have created a set up to pull the names of the missing data into one column called missing test data.

I also have a month/date column. I am trying to calculate the number of coils per month that have missing data.

On a separate sheet I have a month data column and have tried to do sumif and countif and keep getting incorrect argument set.

formula:

=COUNTIF({month-year}, [Month-Year]@row, {missing})

Initial sheet:

count sheet:

Ultimately I am trying to set it up so I can chart the number of coils per month, # of missing test pieces per month, missing test pieces by count (pareto), but can't get past the first one.

on the initial sheet the missing test data column is a column formula, not sure if that has anything to do with it.

I have also tried sumif, count, sum all with same issue.

Find more posts tagged with

Accepted answers

Jake Gustafson

=COUNTIF({Month-Year}, [Month-Year]@row)

I think you were using too many arguments.

All comments

Jake Gustafson

=COUNTIF({Month-Year}, [Month-Year]@row)

I think you were using too many arguments.

Mike TV

@sanscritt

Your COUNTIF formula has too many arguments. COUNTIF only allows for the range to review followed by the criteria to review it by. You have an extra argument in there which is why that is failing. COUNTIFS will allow for extra arguments, if that's what you need.

On your count sheet, can you please provide an example of how you would want the end results to look like? Then we can help get you there. Could it be something like this?

Mike TV

Based on how you want the final report to look, you may want to make each of those values in my above example as it's own column. Because if you were to say want a report that summed all of the Pencil T Bends for a given year, you wouldn't be able to get there as easily by doing it all in a single cell.

sanscritt

I am trying to count the number of occurrences by month. on the count sheet I am trying to match the month on the initial sheet to the month on the count sheet with the output being the number of occurrences.

Example for June, there were 6 different coils that had at least one data point missing. I want the count sheet to look at the initial sheet at June, count the occurrences and return that count to the June cell on the count sheet.

the goal is to be able to chart the number of occurrences by month.

Mike TV

@sanscritt

Then the answer Jake gave you will simply output the number 17, if you don't need to track the individual types of missing thingamabobs.

sanscritt

I will need to track the individual, but could not figure out the initial.

I apologize for the basic question, I was really overthinking it and appreciate the help. I will work on the others and see if I can work through them without overthinking.

Jake Gustafson

You could create additional columns and use COUNTIFS formulas to add a second criteria (1 = If Month-Year matches; and 2 = If Type matches). Then you could graph either by total (like the COUNTIF formula will get you) or by individual by adding the various columns to the graph.

sanscritt

https://community.smartsheet.com/discussion/comment/331190#Comment_331190

Actually this would be good, I could not see the picture before, how would I get this?

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.