Formula Help Countifs Percentages

Not so formula savvy

I need a formula to calculate sets of specific Date columns, and tally those date columns into a % of that set?

For e.g. I have 2 groups. Each group has specific columns that make up the set for each group. There is a unique separate column that will tally the IMP Dates entered (specific to the Group).

I need a formula in the "Group 1%" column that is a tally of the dates populated in the "IMP" columns only for the Group Set

"Group 1 %"Column:

Group 1 set (3): Cartersville IMP Date; ATL IMP Date; Mountainside IMP Date

Group 2 % Column:

Group 2 set (4): Walton IMP Date; PCRN IMP Date; PCRM IMP Date; Newnan IMP Date

I hope that made sense…??

% fx.PNG

@Paul Newcome 😑

jmyzk_cloudsmart_jp

Hi @Not so formula savvy

That's an interesting handle name!

You can use COUNIF({range}, ISDATE(@cell)) format to count the number of cells that have date values.

If the columns of the range are adjacent to each other, as in Group 2 of the demo sheet below, the range can be used as follows;

[Actual IMP Date (Walton)]@row:[Actual IMP Date (Newnan)]@row

However, as your Group 1 example shows, if there are other columns between the columns you want to aggregate, you must calculate COUNTIF(column@row, ISDATE(@cell)) for each and add the results.

[% Complete ATL Region]

=([Group 1 %]@row * 3 + [Group 2 %]@row * 4) / 7

[Group 1 %]

=(

COUNTIF([Actual IMP Date (PAH-ATL)]@row, ISDATE(@cell)) +

COUNTIF([Actual IMP Date (PCH–Cartersville)]@row, ISDATE(@cell)) +

COUNTIF([Actual IMP Date (PMH–Mountainside)]@row, ISDATE(@cell))

) / 3

[Group 2 %]

=COUNTIF([Actual IMP Date (Walton)]@row:[Actual IMP Date (Newnan)]@row, ISDATE(@cell)) / 4

https://app.smartsheet.com/b/publish?EQBCT=5b067ecc536743eeb5cadac99a7de534

Paul Newcome

@Not so formula savvy I would just add a series of IF statements together and then divide by the total.

=(IF([Date1]@row <> "", 1, 0) + IF([Date2]@row <> "", 1, 0) + IF([Date3]@row <> "", 1, 0)) / 3

Paul Newcome

Ok. Does this work?

=(IF(ISDATE([Date1]@row), 1, 0) + IF(ISDATE([Date2]@row), 1, 0) + IF(ISDATE([Date3]@row), 1, 0)) / (3 - (IF([Date1]@row = "N/A", 1, 0) + IF([Date2]@row = "N/A", 1, 0) + IF([Date3]@row = "N/A", 1, 0)))

Paul Newcome

It looks like it may be a missed parenthesis on my part. Try this:

=IFERROR((IF(ISDATE([Actual IMP Date (PAH-ATL)]@row), 1, 0) + IF(ISDATE([Actual IMP Date (PCH-Cartersville)]@row), 1, 0) + IF(ISDATE([Actual IMP Date (PMH-Mountainside)]@row), 1, 0)) / (3 - (IF([Actual IMP Date (PAH-ATL)]@row = "N/A", 1, 0) + IF([Actual IMP Date (PCH-Cartersville)]@row = "N/A", 1, 0) + IF([Actual IMP Date (PMH-Mountainside)]@row = "N/A", 1, 0))), 0)

jmyzk_cloudsmart_jp

Hi @Not so formula savvy

That's an interesting handle name!

You can use COUNIF({range}, ISDATE(@cell)) format to count the number of cells that have date values.

If the columns of the range are adjacent to each other, as in Group 2 of the demo sheet below, the range can be used as follows;

[Actual IMP Date (Walton)]@row:[Actual IMP Date (Newnan)]@row

However, as your Group 1 example shows, if there are other columns between the columns you want to aggregate, you must calculate COUNTIF(column@row, ISDATE(@cell)) for each and add the results.

[% Complete ATL Region]

=([Group 1 %]@row * 3 + [Group 2 %]@row * 4) / 7

[Group 1 %]

=(

COUNTIF([Actual IMP Date (PAH-ATL)]@row, ISDATE(@cell)) +

COUNTIF([Actual IMP Date (PCH–Cartersville)]@row, ISDATE(@cell)) +

COUNTIF([Actual IMP Date (PMH–Mountainside)]@row, ISDATE(@cell))

) / 3

[Group 2 %]

=COUNTIF([Actual IMP Date (Walton)]@row:[Actual IMP Date (Newnan)]@row, ISDATE(@cell)) / 4

https://app.smartsheet.com/b/publish?EQBCT=5b067ecc536743eeb5cadac99a7de534

Paul Newcome

@Not so formula savvy I would just add a series of IF statements together and then divide by the total.

=(IF([Date1]@row <> "", 1, 0) + IF([Date2]@row <> "", 1, 0) + IF([Date3]@row <> "", 1, 0)) / 3

Not so formula savvy

challenge:

I apologize…I forgot to mention in these scenarios, there will be a "N/A" value n some of the columns, and I don't want the "N/A" to count against the total % completed when a Date is not applicable for that location.

see screen shots of an incorrect vs a correct % calculation desired.

Incorrect % e.g..PNG

Correct % e.g.PNG

Let me know if this is possible?

Paul Newcome

Ok. Does this work?

=(IF(ISDATE([Date1]@row), 1, 0) + IF(ISDATE([Date2]@row), 1, 0) + IF(ISDATE([Date3]@row), 1, 0)) / (3 - (IF([Date1]@row = "N/A", 1, 0) + IF([Date2]@row = "N/A", 1, 0) + IF([Date3]@row = "N/A", 1, 0)))

Not so formula savvy

I tried the revised fx and for this group where IMP was N/A for all sites, I get this message in the % column: "#DIVIDE BY ZERO" - is this correct? is it possible to show a different message? maybe like "N/A" since all were N/A's?? if not possible, its ok….i just have to make note so others can interpret…

see screen shots

sample 1.PNG

sample 2.PNG

Paul Newcome

For the divide by zero error, try wrapping the whole thing in an IFERROR.

=IFERROR(existing_formula, 0)

Not so formula savvy

Paul, don't do me like that…LOL…like this?😁

Not sure i know how to "wrap" with the formula i have below:???

=(IF(ISDATE([Actual IMP Date (PAG|PAM-Augusta)]@row), 1, 0) + IF(ISDATE([Actual IMP Date (PMM|PMN-Macon)]@row), 1, 0)) / (2 - (IF([Actual IMP Date (PAG|PAM-Augusta)]@row = "N/A", 1, 0) + IF([Actual IMP Date (PMM|PMN-Macon)]@row = "N/A", 1, 0)))

Paul Newcome

Haha. Sorry about that.

Say this is your existing formula

=(IF(ISDATE(……………………………………….., 1, 0)))

To wrap it in an IFERROR you would end up with

=IFERROR(IF(ISDATE(……………………………………….., 1, 0))), 0)

The way I usually do this in a sheet is to go to the very beginning and put IFERROR( immediately after that initial equals. Then go to the very end and put in ", 0)" (without the quotes / quotes only used to show the starting comma is part of it). Nothing that you already have should change. Just ignore what's in the middle. Put the function (in this case the IFERROR function) at the start and the output at the end.

Not so formula savvy

i did and getting an UNPARSEABLE Error😫

this is my formula below?

=IFERROR(IF(ISDATE([Actual IMP Date (PAH-ATL)]@row), 1, 0) + IF(ISDATE([Actual IMP Date (PCH-Cartersville)]@row), 1, 0) + IF(ISDATE([Actual IMP Date (PMH-Mountainside)]@row), 1, 0)) / (3 - (IF([Actual IMP Date (PAH-ATL)]@row = "N/A", 1, 0) + IF([Actual IMP Date (PCH-Cartersville)]@row = "N/A", 1, 0) + IF([Actual IMP Date (PMH-Mountainside)]@row = "N/A", 1, 0))), 0)

Paul Newcome

I'm not seeing anything right off. Can you provide a screenshot of the formula open in the sheet as if you are about to edit it?

Not so formula savvy

see attached:

sample 3.PNG

Paul Newcome

It looks like it may be a missed parenthesis on my part. Try this:

=IFERROR((IF(ISDATE([Actual IMP Date (PAH-ATL)]@row), 1, 0) + IF(ISDATE([Actual IMP Date (PCH-Cartersville)]@row), 1, 0) + IF(ISDATE([Actual IMP Date (PMH-Mountainside)]@row), 1, 0)) / (3 - (IF([Actual IMP Date (PAH-ATL)]@row = "N/A", 1, 0) + IF([Actual IMP Date (PCH-Cartersville)]@row = "N/A", 1, 0) + IF([Actual IMP Date (PMH-Mountainside)]@row = "N/A", 1, 0))), 0)

Not so formula savvy

its now showing as a 0%…. not sure if that is what i prefer it to show only because it can be misleading, in that, a 0% indicates nothing was done, instead of implying that nothing is needed…

If there isn't a way to return a "N/A" in these scenarios (although not common) when an entire group doesn't require to have any dates applied…then i will use the previous fx that shows the #DIVIDE message…this way at least i know that n/a was applied to all the locations within the group…

I hope i didn't stress you out with these…but thank you for all your help as always…!

If you do think of a fx that would deliver a N/A when All columns within a group reads N/A…that would be great…thanks Paul😊

Paul Newcome

Try swapping that very last zero out for "N/A".

Not so formula savvy

it didn't like that and is giving an Unparseable error…:(…ill use the original fx that works with the #Divide error message…that works best for now…but do keep me posted if u ever randomly figure this out…thanks again!!! 😊