How do I calculate blank cells by month
I have been tasked to calculate certain segment fields into a bar chart. One of the fields require me to calculate the total number of blank fields for each month. This would require me to compare the date file (which has date in it) to the date processed column (some which are blank).
For example, if the date file field has a date of 3/21/2021 in it and the date processed field is blank, then this needs to be counted for the month of March.
I attempted using the following formula, but it seems that the results are incorrect. Could someone take a look and see what I am missing.
=COUNTIFS({Accrue Funding/Enrollment File Tracking Range 1}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2021), {Accrue Funding/Enrollment File Tracking Range 3}, "")
Best Answer
-
Happy to help. We can get both formulas, if you like. For your Apr2 assignment- the screenshot with the colored column names that I provided earlier should get you want you need IF the data is all within the same sheet.
Will you need to provide this information each month? If yes, you could set up what some call a metrics sheet or summary sheet, in order to easily collect data month by month and/or year by year. This will eliminate the need to modify the formula each month to adjust the month number.
If the formula is in a different sheet, I'll call that your source sheet since it is the source of your data, the formula will look like this
=COUNTIFS({source sheet Data File Expected}, IFERROR(MONTH(@cell), 0) = 3, {source sheet Data File Expected}, IFERROR(YEAR(@cell), 0) = 2021, {source sheet Complete}, 1)
Since this is a cross sheet reference you can't just copy paste but will have to create the references individually.
Let me know how this turns out. We'll keep working together until you have exactly what you need. Ask as many questions as you like, that helps the entire community grow.
Kelly
Answers
-
Hey @Beronica Muller
The syntax of your formula looks correct, without seeing your data. When you say
it seems that the results are incorrect
does this mean that the formula is returning an incorrect value? Have you entered non-date values (like 'to be determined') in the date processed field that would count as a non-blank field?
Try this
=COUNTIFS({Accrue Funding/Enrollment File Tracking Range 1}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2021), {Accrue Funding/Enrollment File Tracking Range 3}, NOT(ISDATE(@cell)))
When I'm trouble-shooting I try to eliminate terms from my formulas so I can pinpoint which of my criteria is causing issues. You could try this technique with your formula if ISDATE does not help.
Let me know what happens with ISDATE and we'll keep working it
Kelly
-
The formula is returning an incorrect value for the month of April. To be more accurate, I think that I will need to create a formula to calculate the date filed field and the completed column. However, the completed column is a check box column. I have never created a formula to count check boxes but I tried this and received an #UNPARSEABLE error message. Could you please assist with this formula?
=COUNTIFS([Accrue Funding/Enrollment File Tracking Range 4]:[Accrue Funding/Enrollment File Tracking Range 5], "Complete", @row, 1).
-
Here is the another formula that I tried and it did not work as well.
=COUNTIFS([Date File Expected]:[Date File Expected], IFERROR(MONTH(@cell), 0) = 3, [Date File Expected]:[Date File Expected], IFERROR(YEAR(@cell), 0) = 2020, [Complete]:[Complete], 0)
Also, here is a screenshot of my smartsheet.
-
Hey @Beronica Muller
Sorry- somehow I missed your 4/2 question. I tried the formula in my test sheet and it works.
=COUNTIFS([Date File Expected]:[Date File Expected], IFERROR(MONTH(@cell), 0) = 3, [Date File Expected]:[Date File Expected], IFERROR(YEAR(@cell), 0) = 2020, [Complete]:[Complete], 0)
From your screenshot I see your data is 2021 however you are pulling 2020 data in your formula. Is that intentional? When you say your formula doesn't work - Did you get a zero, an incorrect value, or an error?
What about your 4/2 formula- it looks like it has some cross sheet ranges in it? I'm confused with the square brackets.
Did you get your original cross sheet formulas to work?
Kelly
-
That's okay. I thought with it being Good Friday, you guys were off.
Yes, the original cross sheet formulas did work, however, it did not give me the actual results that I needed. I was under the impression that the complete column and the date processed column would give me the same results.
I am pulling data from 2021 and my formula in smartsheet has 2021. I guess when I was retyping it here, I entered 2020. I received an #UNPARSEABLE error message. My apologies, I have not had my cup of coffee this morning. Please disregard the 4/2 formula as I was trying to attempt to create a similar formula I saw on another smartsheet discussion. EPIC fail 😄
-
@KDM other than the incorrect year, can you identify any other errors in my formula?
-
Are you still getting an UNPARSEABLE? That's usually a comma or parenthesis error. I didn't notice the square brackets on your Complete column before. Those are unnecessary since the column name does not contain a space, special character or numbers.
=COUNTIFS([Date File Expected]:[Date File Expected], IFERROR(MONTH(@cell), 0) = 3, [Date File Expected]:[Date File Expected], IFERROR(YEAR(@cell), 0) = 2021, Complete:Complete, 0)
Are your column names colored in the formula? Can you match the colored parentheses in the formula
Kelly
-
-
One by one, erase the column name and insert it by clicking into a cell in that column. Delete the row reference at the end of the name. The column names are colored when all column names are correct.
-
@KDM I am not sure if I follow. My apologies but this formula is getting the best of me. 😒
As requested, I erased the column name, clicked into a cell in that column, and deleted the row reference at the end of each name. Here is what the formula looks like now. I am still receiving the #UNPARSEABLE error.
=COUNTIFS({Accrue Funding/Enrollment File Tracking Range 4}:{Accrue Funding/Enrollment File Tracking Range 6}, {Accrue Funding/Enrollment File Tracking Range 4}:{Accrue Funding/Enrollment File Tracking Range 6}, {Accrue Funding/Enrollment File Tracking Range 7}:{Accrue Funding/Enrollment File Tracking Range 7}, 0)
-
This is a cross sheet reference? Not the same columns as the screenshot? There is a different syntax to cross sheet references
The big difference is you don't use the colon to indicate a range. There is only a single entry. If the range does encompass multiple columns, then you set that up in the original range (you highlight the two contiguous columns before you click the Insert Reference button). A good practice is to name your ranges to designate specific columns rather than keeping the generic smartsheet range numbers. Do this before clicking the Insert Reference button.
Sorry but I cannot interpret what you are trying to do. Originally we were here:
=COUNTIFS({Accrue Funding/Enrollment File Tracking Range 1}, AND(IFERROR(MONTH(@cell), 0) =1, IFERROR(YEAR(@cell), 0) = 2021), {Accrue Funding/Enrollment File Tracking Range 3}, NOT(ISDATE(@cell)))
In the formula above I assumed each range was a single column. The curly brackets tell me the range is a different sheet than where the formula is being inserted. This says look at Range 1 (which should be a date column) and count rows that are from Jan 2021 AND that also have No Date in Range 3 (which should also be a date column).
Use the formula I just inserted as a format for your formula. Or tell me in words what you are trying to accomplish and I'll build it for you.
ANYTIME you are inserting a formula into a cell, there is a formula helper. You should be able to click on a term in between commas and see what highlights in the helper below. Note if there are more than two range-criteria pairs you're in the '...' of the [range2,criterion2,...] and it will keep showing range2 highlight. Use the helper to keep up with where you are in a formula.
By the way, THIS formula above is where I wanted you to make sure the column names were colored. Cross sheet references are not colored.
-
@KDM thank you so much for explaining this to me. When I originally began this post, it was requested that I create a formula that would count the total number of fields from the requested columns in the formula including blank fields for each month. To do this, it would require me to compare the date file expected field column (which has date in it) to the date processed column (which some fields are blank).
On April 2, my task then changed to calculating the fields that are in the date file expected column and the complete column (a check box column). For example, if the date file expected column has 100 dates total for the month March, I will need to create a formula to count the number of checked and unchecked boxes for all dates in March that would equal the 100 total dates.
Since my formulas are confusing, I think that it is best that you provide the formula.
-
Happy to help. We can get both formulas, if you like. For your Apr2 assignment- the screenshot with the colored column names that I provided earlier should get you want you need IF the data is all within the same sheet.
Will you need to provide this information each month? If yes, you could set up what some call a metrics sheet or summary sheet, in order to easily collect data month by month and/or year by year. This will eliminate the need to modify the formula each month to adjust the month number.
If the formula is in a different sheet, I'll call that your source sheet since it is the source of your data, the formula will look like this
=COUNTIFS({source sheet Data File Expected}, IFERROR(MONTH(@cell), 0) = 3, {source sheet Data File Expected}, IFERROR(YEAR(@cell), 0) = 2021, {source sheet Complete}, 1)
Since this is a cross sheet reference you can't just copy paste but will have to create the references individually.
Let me know how this turns out. We'll keep working together until you have exactly what you need. Ask as many questions as you like, that helps the entire community grow.
Kelly
-
@KDM I think this is what I needed. Thank You!
-
Hey @Beronica Muller
Glad you made it work. Shout out if the #Incorrect Argument or other errors pop up and tweak the formula.
Kelly
Help Article Resources
Categories
Check out the Formula Handbook template!
