maximum occurances in a date range

nelsok

Board Regular
Joined
Jan 20, 2006
Messages
166
I am trying to get the count the number of occurances between date ranges. Is this the best way or is there a better way?

The formula is returning 0... i tried counta but that returns the number of rows in the array.
Book1
BCDEFGHIJK
1Time RequestedRequested ETCActual ETCClosedTech TimeRelated
21/2/06 13:301/4/06 13:00NULL1/3/06 17:000OtherOther
31/2/06 17:291/4/06 13:00NULL1/3/06 1:000PreProPrePro2/10/06 0:000
41/3/06 8:211/3/06 11:30NULL1/3/06 11:001ConversionConversion
51/3/06 10:281/3/06 18:00NULL1/3/06 17:150ConversionProd Engineering
61/3/06 11:191/3/06 16:00NULL1/3/06 12:000OtherExport
71/3/06 12:271/3/06 16:00NULL1/3/06 17:300OtherAudit
81/3/06 12:411/3/06 16:00NULLNULL0Prod EngineeringStaging
91/3/06 12:411/4/06 1:00NULLNULL0Export
101/3/06 12:501/4/06 15:00NULL1/3/06 17:000Other
111/3/06 13:181/3/06 14:001/3/06 16:001/3/06 16:000PrePro
121/3/06 13:211/3/06 14:001/3/06 15:001/3/06 15:000PrePro
131/3/06 13:34NULLNULL1/3/06 14:300Audit
141/3/06 13:39NULLNULL1/3/06 14:150Audit
151/3/06 13:581/3/06 17:00NULL1/6/06 22:302Conversion
161/3/06 14:051/3/06 15:00NULL1/3/06 17:001Audit
171/3/06 15:391/3/06 19:00NULL1/3/06 15:450Other
181/3/06 15:411/3/06 17:00NULL1/18/06 16:000Staging
191/3/06 16:161/4/06 10:001/4/06 15:001/4/06 15:002PrePro
201/3/06 16:181/3/06 18:00NULL1/3/06 17:000Audit
211/3/06 17:451/4/05 15:00NULL1/5/06 19:000Other
Sheet1
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi nelsok

You do not explain what you want and I cannot understand your formula.

It looks for
- the maximum value in F (instead of number of occurrences)

- for which column G(Related) equals I2(Other) (should't it equal I3(PrePro), since it's in row 3?)

- and which first of the month of date in column E equals J3 (which never happens since J3 is not a first of the month.

Can you please explain by words what you want?

Like

Number of occurences in column F (Tech Time) for which column G (related) equals the value next to the left in column I and date ???

Anyway this is counting with multiple conditions and can be made with sumproduct.

Best regards
PGC
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Your formula can convert to:

=SUMPRODUCT(--($G$2:$G$5857=I2),--(INT($E$2:$E$5857)-DAY(INT($E$2:$E$5857))+1=J3),$F$2:$F$5857)

but not sure how you define your date range (what's the start date, what's the end date)?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Suppose we have just...
Book1
BCDEFG
1Time RequestedRequested ETCActual ETCClosedTech TimeRelated
21/2/2006 13:301/4/2006 13:00NULL1/3/2006 17:000Other
31/2/2006 17:291/4/2006 13:00NULL1/3/2006 1:000PrePro
41/3/2006 8:211/3/2006 11:30NULL1/3/2006 11:001Conversion
51/3/2006 10:281/3/2006 18:00NULL1/3/2006 17:150Conversion
61/3/2006 11:191/3/2006 16:00NULL1/3/2006 12:000Other
71/3/2006 12:271/3/2006 16:00NULL1/3/2006 17:300Other
81/3/2006 12:411/3/2006 16:00NULLNULL0Prod Engineering
91/3/2006 12:411/4/2006 1:00NULLNULL0Export
101/3/2006 12:501/4/2006 15:00NULL1/3/2006 17:000Other
Sheet1


and nothing else.

What do you need to calculate with respect to Jan-06?
 

nelsok

Board Regular
Joined
Jan 20, 2006
Messages
166

ADVERTISEMENT

i would need to do the following for each day and then i would use another formula to add together all the days in january.. my end goal is to get answers by day week and month.

I need to count the number of occurances by day for each name found in g:g

I need to sum the values in f:f by day which meet each category in g:g


does that make sense?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
i would need to do the following for each day and then i would use another formula to add together all the days in january.. my end goal is to get answers by day week and month.

I need to count the number of occurances by day for each name found in g:g

I need to sum the values in f:f by day which meet each category in g:g


does that make sense?

Which date range should be used -- the one in column B, C or D?
 

nelsok

Board Regular
Joined
Jan 20, 2006
Messages
166

ADVERTISEMENT

I am going to list each date in 2006 in column H and use that cell as the reference.

I am comparing the date in column h to the dates in column E
 

nelsok

Board Regular
Joined
Jan 20, 2006
Messages
166
I am going to list each date in 2006 in column H and use that cell as the reference.

I am comparing the date in column h to the dates in column E
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
I am going to list each date in 2006 in column H and use that cell as the reference.

I am comparing the date in column h to the dates in column E
1.xls
BCDEFGHIJKLMNO
1Time RequestedRequested ETCActual ETCClosedTech TimeRelatedDateOtherPreProConversionProd EngineeringExportAuditStaging
238719.56251/4/2006 13:00NULL1/3/2006 17:000Other1/3/20064120000
338719.728471/4/2006 13:00NULL1/3/2006 1:000PrePro1/6/20060000000
438720.347921/3/2006 11:30NULL1/3/2006 11:001Conversion
538720.436111/3/2006 18:00NULL1/3/2006 17:150Conversion
638720.471531/3/2006 16:00NULL1/3/2006 12:000OtherMonth/YearOtherPreProConversionProd EngineeringExportAuditStaging
738720.518751/3/2006 16:00NULL1/3/2006 17:300Other1-Jan-064120000
838720.528471/3/2006 16:00NULLNULL0Prod Engineering1-Feb-060000000
938720.528471/4/2006 1:00NULLNULL0Export
1038720.534721/4/2006 15:00NULL1/3/2006 17:000OtherDateOtherPreProConversionProd EngineeringExportAuditStaging
111/3/20060010000
121/6/20060000000
Sheet1


I2:

=SUM(IF(ISNUMBER($E$2:$E$10),IF(INT($E$2:$E$10)=$H2,IF($G$2:$G$10=I$1,1))))

Confirmed with control+shift+enter then copied across and down.

I7:

=SUM(IF(ISNUMBER($E$2:$E$10),IF(INT($E$2:$E$10)-DAY(INT($E$2:$E$10))+1=$H7,IF($G$2:$G$10=I$6,1))))

Confirmed with control+shift+enter then copied across and down.

Note the way the month/year of interest in H7 is specified.

I11:

=SUM(IF(ISNUMBER($E$2:$E$10),IF(INT($E$2:$E$10)=$H11,IF($G$2:$G$10=I$1,$F$2:$F$10))))

Confirmed with control+shift+enter then copied across and down.

The first formulas effect a count, the latter a sum.
 

Forum statistics

Threads
1,141,757
Messages
5,708,340
Members
421,566
Latest member
7Nabisco

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
Top