Count Rows Where A Value in Multiple Columns is blank

Peggy S.

Is it possible to count how many lines meet certain criteria AND have a blank value in Column 1 OR Column 2 OR Column 3? For example I have a Smartsheet with a questionnaire and am currently trying to calculate metrics from the answers in said questionnaire. One of my metrics is "Level 1 Completion" (which is equal to "Q1 or Q2 or Q3 has a value, or is not blank") for NeedBy Date in 8 weeks, by location.

For example, in the following table, I would get these:

Reno= 1

Elko= 1

Carson City= 1

I tried:

=COUNTIFS({QuestionnairreLocal}, [Primary Column]@row, {QuestionnaireNeedDate}, @cell <= TODAY(112), {QuestionnaireNeedDate}, @cell >= TODAY(105), (OR({QuestionnaireQ1}, <>"", {QuestionnaireQ2}, <>"", {QuestionnaireQ3}, <>"")).

However when I do this, I get #INVALID DATA TYPE.

Suggestions for if and how I can count the number of rows in my questionnaire Smartsheet that meet this criteria

Cory Page

Hello, i was taking a look at your question but I may need a bit more information as I don't understand the table and expected results you mentioned above.

I broke things down so that we could see each criteria but in my examples I only show Reno as having met your criteria of 8 weeks out, with 1 blank value? Above you said all 3 locations should flag as 1, can you please explain a little more about your criteria.. Thanks,

Peggy S.

Yes!

It may be easier to focus on Carson City as that is mainly my issue:

Carson City has two rows with dates within my 8 week range, however none of the questions have been answered in one row (i.e. Q1, Q2, or Q3 lack an answer/or are blank).

For Reno, they each have an answer in Q1, or Q2, or Q3, but one row is outside the 8 week window.

I'm looking for # of rows (not cells) that meet criteria (location, 8 weeks) where Q1, OR Q2, OR Q3 "is not blank".

Does that clarify my issue?

Cory Page

Am I getting closer?

In the current sheet we have 0 locations that meet that criteria so a count would be 0?

Locations within the 8 week window that do not have any "Blank Values" in Q1 - Q3 should be 0?

If we ignored the 8 week criteria then the only one that would count would be Reno Row 1 in this case.

I could be getting mixed up with the "where Q1, OR Q2, OR Q3" as I am thinking they all need to be "Not blank" in order to count. Sorry, just trying to help.

Peggy S.

I think my original example wasn't clear:

I want to count how many rows in my sheet meet the following criteria:

Needby Date is between 8-9 weeks

There is a value in Q1 or Q2 or Q3 (either "Yes" or "No") (any can be blank, but a minimum of 1 of them should have a value).

And I want to show this in a table represented by Location.

Line 1: Location is Reno, NeedBy Date does not fall between 8-9weeks, therefore not counted.

Line 2: Location is Reno, NeedBy Date falls between 8-9 weeks, and has an answer in Q2, and Q3, so it's counted.

Reno= 1

Line 3: Location is Elko, NeedBy Date falls between 8-9 weeks, and has an answer in Q3, so it's counted.

Elko = 1

Line 4: Location is Carson City, NeedBy Date falls between 8-9 weeks, and does not have an answer for Q1 or Q2, or Q3, so it's not counted.

Line 5: Location is Carson City, NeedBy Date falls between 8-9 weeks, and has an answer in Q1 and Q3, so it's counted.

Carson City = 1

So, if this was in a tabular form it would be (ultimately I am looking for the formular for "Level 1 Completion that calculates the above automatically):

Cory Page

Perfection, sorry that took a bit to understand. I have something quick to show you that might work pending on if you can add a helper or not. Let me know if there is anything you need updated hopefully its closer than previous tries.. :)

@Peggy S. Update: Almost missed the part where you wanted to be in between 8 and 9 weeks, I fixed the formula.

Formula 1: Calculate the 8 to 9 weeks into the future, you could add this formula into the if statement "Formula 2" if you want I kept it separate to keep things simple and clean.

=SUM(NETDAYS(TODAY(), [Need by Date]1) / 7

Formula 2: Calculate the count: The value was needed to sum as this seems to dump the count to a text making it difficult to sum on a different sheet.

=IF([Weeks into the future]@row >= 10, "Falls after 9 weeks", VALUE(IF(IF(AND([Weeks into the future]@row <= 9, [Weeks into the future]@row >= 8), COUNTIFS([Q1]@row:[Q3]@row, <>""), "0") > 0, "1", "0")))

Formula 2: Option without the "Falls after 9 weeks" Flag

=VALUE(IF(IF(AND([Weeks into the future]@row <= 9, [Weeks into the future]@row >= 8), COUNTIFS([Q1]@row:[Q3]@row, <>""), "0") > 0, "1", "0"))

Formula 3: Used for rolling up based on location: I use sumifs by default but you could just do sumif if you want

=SUMIFS([Count of non-blank cells]1:[Count of non-blank cells]5, Location1:Location5, [Other sheet Primary]@row)

Peggy S.

Is there a way to add these helper columns to my calculation sheet, and not have them in the main questionnaire sheet? I believe that is my biggest roadblock. Due to capacity, I'm limited on adding columns to my questionnaire sheet.

Cory Page

@Peggy S. It would be a pretty crazy formula if we wanted to do this without a helper but I think I have an idea.

Q = Once questionnaire is updated in the main sheet will this data ever change or is it locked in forever? I am thinking we might be able to simply set up some automation to copy the main sheet over to a second sheet that can then be used for our helpers.

Q = Do you use Webforms to populate this data or is the sheet directly access. If you use webforms these helpers wouldn't need to be shown on the form and if the helpers are set to Column formulas once the form is submitted the formula's would simply update. Just curious.

If the Q1 - 3 columns were vertical rather than horizontal it would make things a bit easier the layout of the data is a bit tricky.

Update: Yea, the best I can do is get down to one helper column by merging the week count into the one helper. I am sure there is someone out there that might have a solution but I am stumped as to how to make this calculation without a helper or creating a copy automation to keep a secondary sheet for the calculations. If the data is submitted and never changed a secondary sheet would be the best solution I can think of if the primary sheet can't have any more columns added.

=VALUE(IF(IF(AND(SUM(NETDAYS(TODAY(), [Need by Date]1) / 7) <= 9, SUM(NETDAYS(TODAY(), [Need by Date]1) / 7) >= 8), COUNTIFS([Q1]@row:[Q3]@row, <>""), "0") > 0, "1", "0"))

Peggy S.

Thank you! I was able to get permission to add helper cells to the main sheet (questionnaire) and was able to add your original "check if/or" function. I appreciate the insight!

Cory Page

That's great news, the other options would have required a bit more work to manage long term.. Glad I was able to help..