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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
would that not be a counifs()
the number you want to count in R44 and the result in S44

so in S44

=COUNTIFS(G:G, R44, A:A,">="&date_start, A:A,"<="&date_end )
 
Upvote 0
I get nothing in R44 and "0" in S44
This is what I have in S44; =COUNTIFS(G:G, R44, A:A,">="&1/1/2024, A:A,"<="&12/31/2024)
 
Upvote 0
i must have misunderstood what you are after
would not expect anything in R44 - as i said
the number you want to count in R44
Click to expand...
perhaps - some more details and expected results

Therefore -

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.
www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...
www.mrexcel.com www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.
www.mrexcel.com

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days
www.mrexcel.com www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone
 
Upvote 0
I actually do have that. Haven't used it in awhile. I'll post it here in a minute.
 
Upvote 0
Here's what I have and expect in R44 and S44. Disregard the conditional format in G40.

powerball_newcard 10623.xlsm
ABCDEFGHIJKLMNOPQRS
14/22/202423
24/22/20241
34/20/202423
44/20/202425
54/17/20247
64/17/20242
74/15/202417
84/15/202423
94/13/202426
104/13/202410
114/10/20242
124/10/202415
134/8/202414
144/8/202423
154/6/202414
164/6/20249
174/3/202413
184/3/202415
194/1/202412
204/1/202423
213/30/202416
223/30/202423
233/27/202423
243/27/20248
253/25/20244
263/25/202423
273/23/202411
283/23/20243
293/20/202424
303/20/20249
313/18/202417
323/18/202416
333/16/202412
343/16/20245
353/13/20245
363/13/20244
373/11/202412
383/11/20245
393/9/202420
403/9/202416
413/6/202416
423/6/202410
433/4/20244NUMOCC
443/4/2024262310
453/2/202410
463/2/202412
472/28/20247
482/28/20246
492/26/20248
502/26/202416
512/24/202417
522/24/20243
532/21/202411
542/21/202414
552/19/202414
562/19/202417
572/17/202418
582/17/202421
592/14/202417
602/14/202418
612/12/20242
622/12/202414
632/10/202415
642/10/20248
652/7/202423
662/7/202417
672/5/202420
682/5/20249
692/3/202413
702/3/202419
711/31/202425
721/31/202414
731/29/202416
741/29/20244
751/27/202416
761/27/202421
771/24/202418
781/24/20248
791/22/202413
801/22/202421
811/20/202413
821/20/202410
831/17/202425
841/17/20242
851/16/202417
861/16/20244
871/13/202417
881/13/202415
891/10/202419
901/10/202411
911/8/202417
921/8/20242
931/6/202422
941/6/202413
951/3/202423
961/3/20248
971/1/20242
981/1/2024
Sheet4
Cell Formulas
RangeFormula
R44R44=MODE(G2:G98)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G40Expression=COUNTIFS($B$41:$B$529,$B40,$C$41:$C$529,$C40,$D$41:$D$529,$D40,$E$41:$E$529,$E40,$F$41:$F$529,$F40,$G$41:$G$529,$G40)>1textNO
 
Upvote 0
so you have 23 in column G 10 times
is that the answer you need

23 entered in R44 and the result in S44

=COUNTIF(G:G,23)
OR
as you have MOD in R44 to find the highest frequency then
=COUNTIF(G:G,R44)


OR what ?

what happened to the date ?
 
Last edited:
Upvote 0
etaf said:
so you have 23 in column G 10 times
is that the answer you need

23 entered in R44 and the result in S44

=COUNTIF(G:G,23)

OR what ?

what happened to the date ?
Click to expand...
I just use the dates in A:A for the present year as my range for the R44 and S44, I don't actually display them. Sorry for the confusion.
 
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,884
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