Using a Count function with Date Ranges in multiple cells

[jgj1988]

I have 2 files - The first has Sku numbers and months, the second file has sku numbers and date ranges in two cells. they look like this.

FILE 1:


1 2


SKUABC Mar

SKU123 FEB


FILE 2:


1 2 3


SKUABC 1/1/2013 3/1/2013

SKUABC 3/2/2013 4/1/2013

SKUABC 12/1/2012 5/1/2013


Is it possible to use a count function to return 2 since SKUABC in March appears in the second and third row?


Thank you,


James


*File 2 is 3 Columns wide with starting date in column b and end date in column c

 

[SirJB7]

Hi, jgj1988!


It's possible and there are different solutions that depend on which type of data is that of file 1, active worksheet, column B.


If column B has a date value formatted as "mmm" you can do this in C2:

=COUNTIFS('[File1.xlsx]Sheet1!A:A,A2,'[File1.xlsx]Sheet1!B:B>=DATE(YEAR(B2),MONTH(B2),1),'[File1.xlsx]Sheet1!B:B<DATE(YEAR(B2),MONTH(B2)+1,1))

This solution is independent of your regional settings configuration.


If column B has a text value you can do this in C2:

=COUNTIFS('[File1.xlsx]Sheet1!A:A,A2,'[File1.xlsx]Sheet1!B:B>=DATEVALUE("01"&B2&YEAR(TODAY())),'[File1.xlsx]Sheet1!B:B<=EOMONTH(DATEVALUE("01"&B2&YEAR(TODAY()),1),0)

This solution is tied to the regional setting configuration as the name of the months vary and as no year is specified it'd count those occurrences of the indicated month but only of the current year.


Regards!

 

[Debraj]

Hi James,


I am little bit confused with your Date Format.. as none of the date contain greater than 12..


* If they are dd/mm/yyyy then March Count is 0.. (but you have mention 2)

* so they are mm/dd/yyyy then March Count is 3.. (but you have mention 2nd & 3rd row) where I found All Row contain coz.. in 1st Row's End date is 1-MAR-13 which is touching March) and last one contain March from 2012-March.. otherwise 2 is OK


Can you please check the sample and confirm.. (with a helper column.. can be removed)


* Helper Column :

=(MEDIAN(G3,H3+1,EOMONTH(1&$G$7,0)+1)-MEDIAN(G3,H3+1,DATEVALUE(1&$G$7))>0)*1

Drag Down


* and count Total TRUE value..


https://dl.dropboxusercontent.com/u/78831150/Excel/Month%20Appear%20Count%20%28James%29.xlsx


Regards,

Deb


EDIT:

Formula Edited..


If you want to count total No Of Days Overlaps.. Use Below Formula..


=MEDIAN(G2,H2+1,EOMONTH(1&$G$6,0)+1)-MEDIAN(G2,H2+1,DATEVALUE(1&$G$6))

 

[Debraj]

Hi James!


Please discard above.. as its huge confusing..


Try this.. as a Helper Column

=(MEDIAN(StartDate,EndDate+1,EOMONTH(1&CheckMonth,0)+1)-MEDIAN(StartDate,EndDate+1,DATEVALUE(1&CheckMonth))>0)*1

Regards,

Deb

 

[Faseeh]

HI jgj1988,


Try this one as well:


Assume following data in A1:B2...

[pre]

SKUABC	Mar
SKU123	FEB
..and following in D1:F3...

[code]SKUABC	1-Jan-13	1-Mar-13
SKUABC	2-Mar-13	1-Apr-13
SKU123	1-Feb-12	1-May-13

[/pre]
..Enter this one in G1 and drag down:


=SUMPRODUCT(($D$1:$D$3=A1)*(MONTH($E$1:$F$3)=MONTH("1-"&B1&"-2013&#34)*1)[/code]


Regards,

 

[jgj1988]

Thanks guys, I just tried Faseeh's suggestion but im getting a #value error in the first month fomrula. *(MONTH($E$1:$F$3)


I changed the date format to match they way his date was displayed. Anyone know what im doing wrong?

 

[SirJB7]

Hi, jgj1988!

As I wrote in my first post "It's possible and there are different solutions that depend on which type of data is that of file 1, active worksheet, column B.", and here there might be an issue with the data, so consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.

Regards!

 

[Faseeh]

HI jgj1988,


Please see this file and consider what SirJB7 just said if it doesn't fulfill the purpose:


https://dl.dropboxusercontent.com/u/60644346/Count%20with%20Date%20Ranges%20in%20multiple%20cell.xlsx


@SirJB7: Hi!!


Regards,