Formula Help Countifs Percentages
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โฆ??
@Paul Newcome ๐
Best Answers
-
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
-
@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
-
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)))
-
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)
Answers
-
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
-
@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
-
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.
Let me know if this is possible?
-
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)))
-
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
-
For the divide by zero error, try wrapping the whole thing in an IFERROR.
=IFERROR(existing_formula, 0)
-
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)))
-
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.
-
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)
-
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?
-
see attached:
-
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)
-
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๐
-
-
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!!! ๐
Help Article Resources
Categories
Check out the Formula Handbook template!