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 😑

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

@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)))

Formula Help Countifs Percentages

Answers

Paul Newcome Community Champion

03/26/25

This doesn't work?
=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))), "N/A")

CERTIFIED SMARTSHEET PLATINUM PARTNER
10x Time Savers (An exclusive group that grants you access our Smartsheet master classes, tips, tricks, & much more!)
Would you benefit from getting my help in person?
0 · Share on Facebook Share on Twitter

Try for free

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

How to return the last recorded date in a column?
I have a % Complete column and a deadline column. I'm trying to write a formula for when [% Complete]16 equals 100% it returns the last recorded deadline. I'll use this to record the end date of a pro…
Answered ✓
Formulas and Functions
11
Apr 24, 2025
4
What am I doing wrong?
Shows #Incorrect Argument set =IF(AND(IF([Jan Scheduling Location]@row = "BPHO|Belen Public Health Office", [Jan Appt Type]@row, "Err1"), IFERROR(INDEX(DISTINCT(COLLECT([BPHO|Belen Public Health Offic…
Answered ✓
Formulas
Formulas and Functions
20
Apr 24, 2025
5
Nested IF Statements to Determine Priority
Hi everyone, I've written nested IF statements for a Risk Register, looking at Probability and Impact to determine the color. It's been a while since I've done this and I'm obviously rusty. Can someon…
Answered ✓
Formulas
Formulas and Functions
22
Apr 24, 2025
5

Formulas and Functions

Formulas and Functions

Formula Help Countifs Percentages

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

How to return the last recorded date in a column?

What am I doing wrong?

Nested IF Statements to Determine Priority