What formula to use to calculate a column with a drop down in another sheet by month.

RebeccaWe · 04/14/22

Hi, I have created a tracking sheet that contains data and issues brought up each month. Now I am wanting to take data from specific columns and have a formula that counts the entries by month in another sheet. I have tried a few different formulas and have been getting errors.

The column I am wanting to calculate has 4 selections, Class 1, Class 2, Class 3 and Class 4. I am wanting to know the total of each at the end of each month.

I would like the total to appear in the below.

Pulled from the below sheet/column

Jeff Reisman · 04/14/22

https://community.smartsheet.com/discussion/comment/326273#Comment_326273

That certainly looks like a date format to me...

But I figured out your issue!! - blank cells in your date column! It can't evaluate a blank value with the MONTH function. So here's what we do to get around that...

First, how does COUNTIFS really work? COUNTIFS formula evaluates its criteria from left to right, and reduces which cells it considers as it evaluates each criteria. So we're first going to tell it to find the cells that are date values, and from those, find the ones that match the proper month number, and from those, count the ones with the right issue:

=COUNTIFS({QA Alert test Sheet Range 3}, ISDATE(@cell), {QA Alert test Sheet Range 3}, MONTH(@cell) = 4, {QA Alert test Sheet Range 2}, "Class 1 - Missing Parts")

😀

Jeff Reisman · 04/14/22

Try this:

=COUNTIFS({Tracking Sheet 1g. Date Opened}, MONTH(@cell) = 4, {Tracking Sheet 1j. Problem Classification}, "Class 1 - Missing Parts")

The items in italics are column references back to the tracking sheet. Smartsheet will help you create them; when you get to =COUNTIFS( a box will pop up - click on "Reference Another Sheet", select your tracking sheet and the column you want to reference.

Katy H · 04/14/22

Hi!

Try this to see if it would work. I made assumptions about the title of the column that indicates class type in your metric sheet. The curly brackets indicate a cross sheet reference and I used the column name to indicate those.

It would be a COUNTIFS formula. Example for counting Class 2 issues for the month of January below.

=COUNTIFS({1j. Problem Classification}, [QUALITY ALERT BREAKDOWN PER CLASS]@row, {1g. Date Opened}, MONTH(@cell)=1)

RebeccaWe · 04/14/22

Hi thank you, I have tried both suggestions and still keep getting an error. I have attached a photo of the formula I am entering.

Katy H · 04/14/22

What error are you getting? The syntax looks good to me as written, but the error will help me diagnose.

Jeff Reisman · 04/14/22

https://community.smartsheet.com/discussion/comment/326260#Comment_326260

What is the error message? Smartsheet error messages give you a good clue as to what is causing the problem.

My first guess would be that your 1g. Date Opened column is not really a Date column or not really date values?

RebeccaWe · 04/14/22

I am getting an INVALID DATA TYPE.

If there is any other information that might help just let me know.

I am at a loss

Katy H · 04/14/22

https://community.smartsheet.com/discussion/comment/326264#Comment_326264

Got it, yes I agree with Jeff, it is likely that. your "QA Alert Test Sheet range 3" is not actually looking at a date column. Double click the column to evaluate the column type and ensure it is capturing dates.

RebeccaWe · 04/14/22

Hi thank you both, I have checked the column is a date column. Still not working.

I will play around some more. Worst case I reformat the sheet.

Jeff Reisman · 04/14/22

https://community.smartsheet.com/discussion/comment/326268#Comment_326268

How are those dates getting into that column? Via formula, link in from another sheet, or manual entry? It's possible that you have a date-type column that is holding text/number values, which would produce the same error. Here's a test you can do to see if that's the problem:

Go to an empty cell in a text/number column in your tracking sheet on a row that has a date in the 1g. Date Opened column. Enter:

=MONTH([1g. Date Opened]@row)

If you get the #INVALID DATA TYPE error then you know there's an issue with your date values.

RebeccaWe · 04/14/22

Thank you Jeff.

The rows in my sheet are populated when someone submits a form. On my form I have the date is a required field. See image of snip from my form.

Is there away around this? Or will I be unable to count entries by month?

Jeff Reisman · 04/14/22

https://community.smartsheet.com/discussion/comment/326273#Comment_326273

That certainly looks like a date format to me...

But I figured out your issue!! - blank cells in your date column! It can't evaluate a blank value with the MONTH function. So here's what we do to get around that...

First, how does COUNTIFS really work? COUNTIFS formula evaluates its criteria from left to right, and reduces which cells it considers as it evaluates each criteria. So we're first going to tell it to find the cells that are date values, and from those, find the ones that match the proper month number, and from those, count the ones with the right issue:

=COUNTIFS({QA Alert test Sheet Range 3}, ISDATE(@cell), {QA Alert test Sheet Range 3}, MONTH(@cell) = 4, {QA Alert test Sheet Range 2}, "Class 1 - Missing Parts")

😀

RebeccaWe · 04/18/22

Thank you Jeff! This solved my problem! I appreciate the help

What formula to use to calculate a column with a drop down in another sheet by month.

Best Answer

Answers

Help Article Resources

Categories