Occurrences of numbers in date range

J

jarhead58

Active Member
Joined
Sep 21, 2017
Messages
367
Office Version
  1. 2021
Platform
  1. Windows
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
 
Sort by date Sort by votes
How about
Fluff.xlsm
AGRS
122/04/202423
222/04/20241
320/04/202423
420/04/202425
517/04/20247
617/04/20242
715/04/202417
815/04/202423
913/04/202426
1013/04/202410
1110/04/20242
1210/04/202415
1308/04/202414
1408/04/202423
1506/04/202414
1606/04/20249
1703/04/202413
1803/04/202415
1901/04/202412
2001/04/202423
2130/03/202416
2230/03/202423
2327/03/202423
2427/03/20248
2525/03/20244
2625/03/202423
2723/03/202411
2823/03/20243
2920/03/202424
3020/03/20249
3118/03/202417
3218/03/202416
3316/03/202412
3416/03/20245
3513/03/20245
3613/03/20244
3711/03/202412
3811/03/20245
3909/03/202420
4009/03/202416
4106/03/202416
4206/03/202410
4304/03/20244NUMOCC
4404/03/2024262310
4502/03/202410
4602/03/202412
4728/02/20247
4828/02/20246
4926/02/20248
5026/02/202416
5124/02/202417
5224/02/20243
5321/02/202411
5421/02/202414
5519/02/202414
5619/02/202417
5717/02/202418
5817/02/202421
5914/02/202417
6014/02/202418
6112/02/20242
6212/02/202414
6310/02/202415
6410/02/20248
6507/02/202423
6607/02/202417
6705/02/202420
6805/02/20249
6903/02/202413
7003/02/202419
7131/01/202425
7231/01/202414
7329/01/202416
7429/01/20244
7527/01/202416
7627/01/202421
7724/01/202418
7824/01/20248
7922/01/202413
8022/01/202421
8120/01/202413
8220/01/202410
8317/01/202425
8417/01/20242
8516/01/202417
8616/01/20244
8713/01/202417
8813/01/202415
8910/01/202419
9010/01/202411
9108/01/202417
9208/01/20242
9306/01/202422
9406/01/202413
9503/01/202423
9603/01/20248
9701/01/20242
9801/01/2024
Data
Cell Formulas
RangeFormula
R44:S44R44=LET(f,FILTER(G1:G500,YEAR(A1:A500=2024)),m,MODE(f),CHOOSE({1,2},m,SUM(--(f=m))))
Dynamic array formulas.
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Fluff said:
How about
Fluff.xlsm
AGRS
122/04/202423
222/04/20241
320/04/202423
420/04/202425
517/04/20247
617/04/20242
715/04/202417
815/04/202423
913/04/202426
1013/04/202410
1110/04/20242
1210/04/202415
1308/04/202414
1408/04/202423
1506/04/202414
1606/04/20249
1703/04/202413
1803/04/202415
1901/04/202412
2001/04/202423
2130/03/202416
2230/03/202423
2327/03/202423
2427/03/20248
2525/03/20244
2625/03/202423
2723/03/202411
2823/03/20243
2920/03/202424
3020/03/20249
3118/03/202417
3218/03/202416
3316/03/202412
3416/03/20245
3513/03/20245
3613/03/20244
3711/03/202412
3811/03/20245
3909/03/202420
4009/03/202416
4106/03/202416
4206/03/202410
4304/03/20244NUMOCC
4404/03/2024262310
4502/03/202410
4602/03/202412
4728/02/20247
4828/02/20246
4926/02/20248
5026/02/202416
5124/02/202417
5224/02/20243
5321/02/202411
5421/02/202414
5519/02/202414
5619/02/202417
5717/02/202418
5817/02/202421
5914/02/202417
6014/02/202418
6112/02/20242
6212/02/202414
6310/02/202415
6410/02/20248
6507/02/202423
6607/02/202417
6705/02/202420
6805/02/20249
6903/02/202413
7003/02/202419
7131/01/202425
7231/01/202414
7329/01/202416
7429/01/20244
7527/01/202416
7627/01/202421
7724/01/202418
7824/01/20248
7922/01/202413
8022/01/202421
8120/01/202413
8220/01/202410
8317/01/202425
8417/01/20242
8516/01/202417
8616/01/20244
8713/01/202417
8813/01/202415
8910/01/202419
9010/01/202411
9108/01/202417
9208/01/20242
9306/01/202422
9406/01/202413
9503/01/202423
9603/01/20248
9701/01/20242
9801/01/2024
Data
Cell Formulas
RangeFormula
R44:S44R44=LET(f,FILTER(G1:G500,YEAR(A1:A500=2024)),m,MODE(f),CHOOSE({1,2},m,SUM(--(f=m))))
Dynamic array formulas.
Click to expand...
I neglected to say that my date format is 3/6/2024 9:55:41 AM in order to get the first one entered according to order, sorry. Secondly, how does the formula include the R44-S44? Still new to using arrays.
 
Upvote 0
Fluff said:
That's what the Choose section does, it outputs both answers
Click to expand...
I am getting a #VALUE in S44. Is it because my DATE format includes the time? Also, how hard is it to use T44 instead of S44, can I edit it somehow?
 
Upvote 0
Do you get the correct value in R44?
 
Upvote 0
In that case use
Excel Formula: 
=LET(f,FILTER(G1:G500,YEAR(A1:A500=2024)),MODE(f))
and then use a countifs to return the number of times it appears.
 
Upvote 0
Ok, somehow I must have deleted a set of parentheses but S44 is coming up with 35 instead of 10. Here is what I have as the formula.
=LET(f,FILTER(G1:G500,YEAR(A1:A500=2024)),m,MODE(f),CHOOSE({1,2},m,SUM(--(f=m))))
 
Upvote 0
I think it happened when I was trying to figure out how to use T44 instead of S44 :(
 
Upvote 0
If you want to use T44 then you will need to use two separate formulas.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Count number of years from series of dates - not occurrences within years but # of years with occurrences
Replies
4
Views
282
Eric W
Eric W
K
  • Solved
last occurrence of a value in a 4 column array
Replies
4
Views
395
kmr47
K
J
  • Solved
First (and last) occurrence of set of values in a row gives corresponding column value
Replies
2
Views
402
jimmychacha
J
M
  • Solved
Dynamically Plot Data Points in Scatterplot
Replies
5
Views
480
felixstraube
F
G
  • Solved
Return Cell references of specific text in range
2
Replies
19
Views
932
smozgur
smozgur

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top