Nesting COUNTIF within an IF function (or the other way around?)

MarnieMaria

I'm trying to get a count by month for two columns if the row matches a criteria. So if the Planning Level Indicator equals "Level 1", count the number of if the date equals January 2022 in the Screen 1 and Screen 2 columns. The answer for the first row should be 1.

But I'm summarizing for the month, so I'm using a cross sheet reference with the following formula (where the range of both columns Screen 1 and Screen 2 is named "Screens":

January Count for Screen 1 and Screen 2=

IF({Planning Level Indicator}@row= "Level 1", COUNTIF({Screens}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2022)))

I think I'm looking at this completely wrong, but have tried several interation and can't seem to figure out where I'm going wrong. Any help is much appreciated!

KPH

Hi @MarnieMaria

Rather than an IF and a COUNTIF, I would use COUNTIFS - that will let you specify all your criteria within one formula.

https://help.smartsheet.com/function/countifs

If you'd like help building the formula can you clarify the logic:

count the number of if the date equals January 2022 in the Screen 1 and Screen 2 columns

Do you mean if screen 1 or screen 2 has a January date then the row should be counted (if it is also level 1)?

KPH

Here are some COUNTIFS that could help you. You will need to replace the column references with your cross sheet references (change [Planning Level Indicator]:[Planning Level Indicator] to {Planning Level Indicator}, for example) but I am using the column names so we can see what we're going.

Example data

This formula will give you the total if:

• [Planning Level Indicator] is Level 1
• and
• [Screen 1] is in January

=COUNTIFS([Planning Level Indicator]:[Planning Level Indicator], "Level 1", [Screen 1]:[Screen 1], IFERROR(MONTH(@cell), 0) = 1)

In the example, this is 3.

This formula will give you the total if:

• [Planning Level Indicator] is Level 1
• and
• [Screen 2] is in January

=COUNTIFS([Planning Level Indicator]:[Planning Level Indicator], "Level 1", [Screen 2]:[Screen 2], IFERROR(MONTH(@cell), 0) = 1)

In the example, this is 2.

This formula will give you the total if:

• [Planning Level Indicator] is Level 1
• and
• [Screen 1] is in January
• and
• [Screen 2] is in January

=COUNTIFS([Planning Level Indicator]:[Planning Level Indicator], "Level 1", [Screen 1]:[Screen 1], IFERROR(MONTH(@cell), 0) = 1, [Screen 2]:[Screen 2], IFERROR(MONTH(@cell), 0) = 1)

In the example, this is 1.

Now if you want a formula will give you the total if:

• [Planning Level Indicator] is Level 1
• and either
• [Screen 1] is in January
• or
• [Screen 2] is in January

That is more problematic - at least the way I am thinking. I would combine all of the formula above.

Adding the first two will give you the count of Level 1s with a January date in Screen 1 and all those with a January date in screen 2.

=COUNTIFS([Planning Level Indicator]:[Planning Level Indicator], "Level 1", [Screen 1]:[Screen 1], IFERROR(MONTH(@cell), 0) = 1) + COUNTIFS([Planning Level Indicator]:[Planning Level Indicator], "Level 1", [Screen 2]:[Screen 2], IFERROR(MONTH(@cell), 0) = 1)

But any row that has a January date in both will be counted twice. So you also need to remove those by subtracting the third formula.

=COUNTIFS([Planning Level Indicator]:[Planning Level Indicator], "Level 1", [Screen 1]:[Screen 1], IFERROR(MONTH(@cell), 0) = 1) + COUNTIFS([Planning Level Indicator]:[Planning Level Indicator], "Level 1", [Screen 2]:[Screen 2], IFERROR(MONTH(@cell), 0) = 1) - COUNTIFS([Planning Level Indicator]:[Planning Level Indicator], "Level 1", [Screen 1]:[Screen 1], IFERROR(MONTH(@cell), 0) = 1, [Screen 2]:[Screen 2], IFERROR(MONTH(@cell), 0) = 1)

In the example, this is 4.

There may be a way to do this more neatly by evaluating two columns in the COUNTIFS with an OR but it isn't coming to me at the moment.

MarnieMaria

Thanks for your detailed response @KPH - it is indeed the third scenario I'm looking for, (the one that is more problematic ☺️) :

• Planning Level Indicator] is Level 1
• and either
• [Screen 1] is in January
• or
• [Screen 2] is in January

It does seem as if there should be a way to do this more neatly, but I'm not seeing it either; Level 2 has six screen columns so that formula would be massive. I've tried using the OR funtion to group the columns, but I get an Invalid Data Type Error.

Thanks again!

Marnie

KPH

Of course you wanted the most difficult option 😉

The issue is the combination of IF with OR on multiple columns. I would usually add a hidden column to the first sheet and evaluate the OR in that (COUNTIF screen 1, screen 2, have January dates), then use the COUNTIFS on the Planning Level Indicator column (is Level 1) and the hidden column (is greater than 0).

I didn't suggest that in this case as I assumed you wanted this to work for more months than just January and this would result in 12 additional columns.

But I think splitting the calculation into two parts using hidden columns is the way forward. Can you confirm you do want this to work for all 12 months? (please say no 😉) And how many levels and screens you have.

MarnieMaria

hahah! Yes, of course I want it to be the most difficult possible and not only for all twelve months, but actually for all of 2022 and 2023, so for 24 months. The helper columns aren't going to be possible. It's also why those really long formulas are problematic, since changing the month and year number for each screen column would get hard to keep accurate.

Thanks again for your help!

KPH

So my thought was to create a helper column like this for each month

=COUNTIF([Screen 1]@row:[Screen 3]@row, IFERROR(MONTH(@cell), 0) = 1)

Then use this to total for Level 1.

=COUNTIFS([Planning Level Indicator]:[Planning Level Indicator], "Level 1", [Jan Count]:[Jan Count], ">0")

Repeating the helper column for February, March, April, etc.

But it sounds like even that won't be enough. When you say you want to do this for 2022 and 2023 how do you want that to look? Right now we are only considering Month when checking the date in the Screen column. Do you need to include year as well? Or should January 22 and January 23 both be included in the same total?

Have you considered using a report instead of a sheet calculation to do what you need? That could be easier.

MarnieMaria

I had included the year in the original formula, so yes, it's each month and year.

I had not considered a report - that may work. It still seems like there should be an easier way to create this formula, but thanks! I'll give the report a try!

-Marnie