I'm using Excel 2021. I'm trying to figure out how many occurrences of numbers in column G within a date range in column A of this year and entering the number in R44 with the highest occurrences. Then enter the number occurrences in S44. I would think VBA would be the way to do it but can't seem to figure out the correct way to do it. TIA
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Occurrences of numbers in date range
- Thread starter jarhead58
- Start date
I'll just leave it as is then. Any idea why it's counting 35 instead of 10? I did notice that the sample I gave you only has 98 cells to look at which would only be 10, the formula goes to 500 but there are no more cells in A99:A500 from 2024 in that range.
Upvote
0
Excel Facts
Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Here is a little more data down to line 200 which includes 2023 data including the number 23 which should be ignored. I only want the current year occurrences of 23.
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| R44:S44 | R44 | =LET(f,FILTER(G1:G200,YEAR(A1:A200=2024)),m,MODE(f),CHOOSE({1,2},m,SUM(--(f=m)))) |
| Press CTRL+SHIFT+ENTER to enter array formulas. | ||
| powerball_newcard 10623.xlsm | |||
|---|---|---|---|
| A | |||
| 2 | 4/22/2024 | ||
| 3 | 4/22/2024 | ||
| 4 | 4/20/2024 | ||
| 5 | 4/20/2024 | ||
| 6 | 4/17/2024 | ||
| 7 | 4/17/2024 | ||
| 8 | 4/15/2024 | ||
| 9 | 4/15/2024 | ||
| 10 | 4/13/2024 | ||
| 11 | 4/13/2024 | ||
| 12 | 4/10/2024 | ||
| 13 | 4/10/2024 | ||
| 14 | 4/8/2024 | ||
| 15 | 4/8/2024 | ||
| 16 | 4/6/2024 | ||
| 17 | 4/6/2024 | ||
| 18 | 4/3/2024 | ||
| 19 | 4/3/2024 | ||
| 20 | 4/1/2024 | ||
| 21 | 4/1/2024 | ||
| 22 | 3/30/2024 | ||
| 23 | 3/30/2024 | ||
| 24 | 3/27/2024 | ||
| 25 | 3/27/2024 | ||
| 26 | 3/25/2024 | ||
| 27 | 3/25/2024 | ||
| 28 | 3/23/2024 | ||
| 29 | 3/23/2024 | ||
| 30 | 3/20/2024 | ||
| 31 | 3/20/2024 | ||
| 32 | 3/18/2024 | ||
| 33 | 3/18/2024 | ||
| 34 | 3/16/2024 | ||
| 35 | 3/16/2024 | ||
| 36 | 3/13/2024 | ||
| 37 | 3/13/2024 | ||
| 38 | 3/11/2024 | ||
| 39 | 3/11/2024 | ||
| 40 | 3/9/2024 | ||
| 41 | 3/9/2024 | ||
| 42 | 3/6/2024 | ||
| 43 | 3/6/2024 | ||
| 44 | 3/4/2024 | ||
| 45 | 3/4/2024 | ||
| 46 | 3/2/2024 | ||
| 47 | 3/2/2024 | ||
| 48 | 2/28/2024 | ||
| 49 | 2/28/2024 | ||
| 50 | 2/26/2024 | ||
| 51 | 2/26/2024 | ||
| 52 | 2/24/2024 | ||
| 53 | 2/24/2024 | ||
| 54 | 2/21/2024 | ||
| 55 | 2/21/2024 | ||
| 56 | 2/19/2024 | ||
| 57 | 2/19/2024 | ||
| 58 | 2/17/2024 | ||
| 59 | 2/17/2024 | ||
| 60 | 2/14/2024 | ||
| 61 | 2/14/2024 | ||
| 62 | 2/12/2024 | ||
| 63 | 2/12/2024 | ||
| 64 | 2/10/2024 | ||
| 65 | 2/10/2024 | ||
| 66 | 2/7/2024 | ||
| 67 | 2/7/2024 | ||
| 68 | 2/5/2024 | ||
| 69 | 2/5/2024 | ||
| 70 | 2/3/2024 | ||
| 71 | 2/3/2024 | ||
| 72 | 1/31/2024 | ||
| 73 | 1/31/2024 | ||
| 74 | 1/29/2024 | ||
| 75 | 1/29/2024 | ||
| 76 | 1/27/2024 | ||
| 77 | 1/27/2024 | ||
| 78 | 1/24/2024 | ||
| 79 | 1/24/2024 | ||
| 80 | 1/22/2024 | ||
| 81 | 1/22/2024 | ||
| 82 | 1/20/2024 | ||
| 83 | 1/20/2024 | ||
| 84 | 1/17/2024 | ||
| 85 | 1/17/2024 | ||
| 86 | 1/16/2024 | ||
| 87 | 1/16/2024 | ||
| 88 | 1/13/2024 | ||
| 89 | 1/13/2024 | ||
| 90 | 1/10/2024 | ||
| 91 | 1/10/2024 | ||
| 92 | 1/8/2024 | ||
| 93 | 1/8/2024 | ||
| 94 | 1/6/2024 | ||
| 95 | 1/6/2024 | ||
| 96 | 1/3/2024 | ||
| 97 | 1/3/2024 | ||
| 98 | 1/1/2024 | ||
| 99 | 1/1/2024 | ||
| 100 | 12/30/2023 | ||
| 101 | 12/30/2023 | ||
| 102 | 12/27/2023 | ||
| 103 | 12/27/2023 | ||
| 104 | 12/25/2023 | ||
| 105 | 12/25/2023 | ||
| 106 | 12/23/2023 | ||
| 107 | 12/23/2023 | ||
| 108 | 12/20/2023 | ||
| 109 | 12/20/2023 | ||
| 110 | 12/18/2023 | ||
| 111 | 12/18/2023 | ||
| 112 | 12/16/2023 | ||
| 113 | 12/16/2023 | ||
| 114 | 12/13/2023 | ||
| 115 | 12/13/2023 | ||
| 116 | 12/9/2023 | ||
| 117 | 12/9/2023 | ||
| 118 | 12/6/2023 | ||
| 119 | 12/6/2023 | ||
| 120 | 12/4/2023 | ||
| 121 | 12/4/2023 | ||
| 122 | 12/2/2023 | ||
| 123 | 12/2/2023 | ||
| 124 | 11/29/2023 | ||
| 125 | 11/29/2023 | ||
| 126 | 11/27/2023 | ||
| 127 | 11/27/2023 | ||
| 128 | 11/25/2023 | ||
| 129 | 11/25/2023 | ||
| 130 | 11/22/2023 | ||
| 131 | 11/22/2023 | ||
| 132 | 11/19/2023 | ||
| 133 | 11/19/2023 | ||
| 134 | 11/15/2023 | ||
| 135 | 11/15/2023 | ||
| 136 | 11/13/2023 | ||
| 137 | 11/13/2023 | ||
| 138 | 11/11/2023 | ||
| 139 | 11/11/2023 | ||
| 140 | 11/8/2023 | ||
| 141 | 11/8/2023 | ||
| 142 | 11/6/2023 | ||
| 143 | 11/6/2023 | ||
| 144 | 11/4/2023 | ||
| 145 | 11/4/2023 | ||
| 146 | 11/1/2023 | ||
| 147 | 11/1/2023 | ||
| 148 | 10/28/2023 | ||
| 149 | 10/28/2023 | ||
| 150 | 10/25/2023 | ||
| 151 | 10/25/2023 | ||
| 152 | 10/23/2023 | ||
| 153 | 10/23/2023 | ||
| 154 | 10/21/2023 | ||
| 155 | 10/21/2023 | ||
| 156 | 10/18/2023 | ||
| 157 | 10/18/2023 | ||
| 158 | 10/16/2023 | ||
| 159 | 10/16/2023 | ||
| 160 | 10/14/2023 | ||
| 161 | 10/14/2023 | ||
| 162 | 10/11/2023 | ||
| 163 | 10/11/2023 | ||
| 164 | 10/9/2023 | ||
| 165 | 10/9/2023 | ||
| 166 | 10/7/2023 | ||
| 167 | 10/7/2023 | ||
| 168 | 10/4/2023 | ||
| 169 | 10/4/2023 | ||
| 170 | 10/2/2023 | ||
| 171 | 10/2/2023 | ||
| 172 | 9/30/2023 | ||
| 173 | 9/30/2023 | ||
| 174 | 9/27/2023 | ||
| 175 | 9/27/2023 | ||
| 176 | 9/25/2023 | ||
| 177 | 9/25/2023 | ||
| 178 | 9/23/2023 | ||
| 179 | 9/23/2023 | ||
| 180 | 9/20/2023 | ||
| 181 | 9/20/2023 | ||
| 182 | 9/18/2023 | ||
| 183 | 9/18/2023 | ||
| 184 | 9/16/2023 | ||
| 185 | 9/16/2023 | ||
| 186 | 9/13/2023 | ||
| 187 | 9/13/2023 | ||
| 188 | 9/11/2023 | ||
| 189 | 9/11/2023 | ||
| 190 | 9/9/2023 | ||
| 191 | 9/9/2023 | ||
| 192 | 9/6/2023 | ||
| 193 | 9/6/2023 | ||
| 194 | 9/4/2023 | ||
| 195 | 9/4/2023 | ||
| 196 | 9/2/2023 | ||
| 197 | 9/2/2023 | ||
| 198 | 8/30/2023 | ||
| 199 | 8/30/2023 | ||
| 200 | 8/28/2023 | ||
Sheet1 | |||
| powerball_newcard 10623.xlsm | |||
|---|---|---|---|
| G | |||
| 2 | 23 | ||
| 3 | 1 | ||
| 4 | 23 | ||
| 5 | 25 | ||
| 6 | 7 | ||
| 7 | 2 | ||
| 8 | 17 | ||
| 9 | 23 | ||
| 10 | 26 | ||
| 11 | 10 | ||
| 12 | 2 | ||
| 13 | 15 | ||
| 14 | 14 | ||
| 15 | 23 | ||
| 16 | 14 | ||
| 17 | 9 | ||
| 18 | 13 | ||
| 19 | 15 | ||
| 20 | 12 | ||
| 21 | 23 | ||
| 22 | 16 | ||
| 23 | 23 | ||
| 24 | 23 | ||
| 25 | 8 | ||
| 26 | 4 | ||
| 27 | 23 | ||
| 28 | 11 | ||
| 29 | 3 | ||
| 30 | 24 | ||
| 31 | 9 | ||
| 32 | 17 | ||
| 33 | 16 | ||
| 34 | 12 | ||
| 35 | 5 | ||
| 36 | 5 | ||
| 37 | 4 | ||
| 38 | 12 | ||
| 39 | 5 | ||
| 40 | 20 | ||
| 41 | 16 | ||
| 42 | 16 | ||
| 43 | 10 | ||
| 44 | 4 | ||
| 45 | 26 | ||
| 46 | 10 | ||
| 47 | 12 | ||
| 48 | 7 | ||
| 49 | 6 | ||
| 50 | 8 | ||
| 51 | 16 | ||
| 52 | 17 | ||
| 53 | 3 | ||
| 54 | 11 | ||
| 55 | 14 | ||
| 56 | 14 | ||
| 57 | 17 | ||
| 58 | 18 | ||
| 59 | 21 | ||
| 60 | 17 | ||
| 61 | 18 | ||
| 62 | 2 | ||
| 63 | 14 | ||
| 64 | 15 | ||
| 65 | 8 | ||
| 66 | 23 | ||
| 67 | 17 | ||
| 68 | 20 | ||
| 69 | 9 | ||
| 70 | 13 | ||
| 71 | 19 | ||
| 72 | 25 | ||
| 73 | 14 | ||
| 74 | 16 | ||
| 75 | 4 | ||
| 76 | 16 | ||
| 77 | 21 | ||
| 78 | 18 | ||
| 79 | 8 | ||
| 80 | 13 | ||
| 81 | 21 | ||
| 82 | 13 | ||
| 83 | 10 | ||
| 84 | 25 | ||
| 85 | 2 | ||
| 86 | 17 | ||
| 87 | 4 | ||
| 88 | 17 | ||
| 89 | 15 | ||
| 90 | 19 | ||
| 91 | 11 | ||
| 92 | 17 | ||
| 93 | 2 | ||
| 94 | 22 | ||
| 95 | 13 | ||
| 96 | 23 | ||
| 97 | 8 | ||
| 98 | 2 | ||
| 99 | 1 | ||
| 100 | 8 | ||
| 101 | 7 | ||
| 102 | 2 | ||
| 103 | 5 | ||
| 104 | 9 | ||
| 105 | 4 | ||
| 106 | 1 | ||
| 107 | 6 | ||
| 108 | 3 | ||
| 109 | 16 | ||
| 110 | 9 | ||
| 111 | 26 | ||
| 112 | 3 | ||
| 113 | 25 | ||
| 114 | 14 | ||
| 115 | 18 | ||
| 116 | 18 | ||
| 117 | 1 | ||
| 118 | 9 | ||
| 119 | 21 | ||
| 120 | 7 | ||
| 121 | 9 | ||
| 122 | 25 | ||
| 123 | 3 | ||
| 124 | 23 | ||
| 125 | 4 | ||
| 126 | 20 | ||
| 127 | 12 | ||
| 128 | 20 | ||
| 129 | 9 | ||
| 130 | 14 | ||
| 131 | 21 | ||
| 132 | 8 | ||
| 133 | 20 | ||
| 134 | 17 | ||
| 135 | 6 | ||
| 136 | 14 | ||
| 137 | 21 | ||
| 138 | 5 | ||
| 139 | 7 | ||
| 140 | 18 | ||
| 141 | 20 | ||
| 142 | 24 | ||
| 143 | 26 | ||
| 144 | 26 | ||
| 145 | 6 | ||
| 146 | 21 | ||
| 147 | 12 | ||
| 148 | 11 | ||
| 149 | 2 | ||
| 150 | 17 | ||
| 151 | 2 | ||
| 152 | 13 | ||
| 153 | 21 | ||
| 154 | 5 | ||
| 155 | 11 | ||
| 156 | 15 | ||
| 157 | 24 | ||
| 158 | 1 | ||
| 159 | 18 | ||
| 160 | 23 | ||
| 161 | 14 | ||
| 162 | 2 | ||
| 163 | 10 | ||
| 164 | 10 | ||
| 165 | 14 | ||
| 166 | 23 | ||
| 167 | 19 | ||
| 168 | 22 | ||
| 169 | 1 | ||
| 170 | 2 | ||
| 171 | 5 | ||
| 172 | 25 | ||
| 173 | 22 | ||
| 174 | 1 | ||
| 175 | 7 | ||
| 176 | 19 | ||
| 177 | 4 | ||
| 178 | 24 | ||
| 179 | 21 | ||
| 180 | 8 | ||
| 181 | 23 | ||
| 182 | 14 | ||
| 183 | 9 | ||
| 184 | 6 | ||
| 185 | 5 | ||
| 186 | 24 | ||
| 187 | 18 | ||
| 188 | 22 | ||
| 189 | 5 | ||
| 190 | 20 | ||
| 191 | 25 | ||
| 192 | 10 | ||
| 193 | 1 | ||
| 194 | 7 | ||
| 195 | 13 | ||
| 196 | 21 | ||
| 197 | 19 | ||
| 198 | 2 | ||
| 199 | 4 | ||
| 200 | 4 | ||
Sheet1 | |||
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| B133:G133,B552:G553,B41:G41,B618:G3143 | Expression | =COUNTIFS($B$41:$B$529,$B41,$C$41:$C$529,$C41,$D$41:$D$529,$D41,$E$41:$E$529,$E41,$F$41:$F$529,$F41,$G$41:$G$529,$G41)>1 | text | NO |
Upvote
0
Thank you, that was it. Really appreciate your help. Have a great day!
Upvote
0
Ooops, I did have another question. Since it is utilizing MODE, can it be filled down showing the 2nd, 3rd, 4th.....occurrences along with the counts?
Upvote
0
Ok, thank you again!
Upvote
0
Similar threads
- Solved
- Solved
