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

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

Formula based of portfolio report
Hi, I would like to do a simple count and countif based of my portfolio report. But when I start the formula and want to link to the report, it is not available for selection. I am the admin/owner of …
Answered ✓
Formulas
summary report
Formulas and Functions
13
3:56 pm
2
Countifs contains
Trying to count if a cell by criteria if another cell contains "device": =COUNTIFS(CONTAINS("Device", [eCOA Department]:[eCOA Department]), [Award Date]:[Award Date], >=DATE(Year#, 3, 1), [Award Date]…
Answered ✓
Formulas and Functions
12
2:23 pm
3
Formula Help
I have been trying to work on a formula within smartsheet and only part of the formula is working and I have been using AI through smartsheet. I need help with tha last 2 parts of this formula to get …
Answered ✓
Education
Formulas
Support
Formulas and Functions
16
12:21 pm
1

Formulas and Functions

Formulas and Functions

Formula Help Countifs Percentages

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Formula based of portfolio report

Countifs contains

Formula Help