• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count Dates Using SumProduct

A

AZExcel

Member
I have a problem that I have been puzzling over for a week or so.

The goal is to create a chart to compare data from 2 sheets.


Each sheet in column A contains sequential dates. The 1st sheet has 2010 dates in 01/01/2010 format thru 12/31/2010. The sheet for 2011 has dates up to 05/12/2011


On the 3 sheet I has set the date criteria for each month and have attempted to use the following formula to count the records per month for each year after which I will compare them. This formula seems to work on sheet1, but for whatever reason it does not work on sheet2.. FYI Sheet3!C! is criteria Jan 11. Any help would be appreciated


SUMPRODUCT((Sheet2!A2:A37>=Sheet3!C1)*(Sheet2!A2:A37<=EOMONTH(Sheet3!C1,0)))
 
If Sheet 1 has 2010 dates and Sheet 2 has 2011 dates

then your problem is the years don't match up


Should it be:

for Sheet2:

=SUMPRODUCT((Sheet2!A2:A366>=DATE(YEAR(Sheet2!A2),MONTH(C1),DAY(C1)))*(Sheet2!A2:A366<=EOMONTH(DATE(YEAR(Sheet2!A2),MONTH(C1),DAY(C1)),0)))


for Sheet1:

=SUMPRODUCT((Sheet1!A2:A366>=DATE(YEAR(Sheet1!A2),MONTH(C1),DAY(C1)))*(Sheet1!A2:A366<=EOMONTH(DATE(YEAR(Sheet1!A2),MONTH(C1),DAY(C1)),0)))


Both these equations need to be used on Sheet 3
 
Hui,


I will try to work with the formulas you suggest, but regarding your reply... Sheet3 contains something similar to this. In my example C1 was Jan-11. My formula only returns 9 records when there are actually 20 records for Jan 2011. What is so frustrating is the same formula is working for sheet1's(2010) data but it is not capturing on the records for Jan 2011 in sheet2


Jan-10 Jan-11

26 9

Feb-10 Feb-11

25 36

Mar-10 Mar-11

42

Apr-10 Apr-11

46

May-10 May-11

27

Jun-10

31

Jul-10

23

Aug-10

19

Sep-10

22

Oct-10

22

Nov-10

23

Dec-10

22
 
So the above will work where ever you copy it on Sheet 3

Assuming that this is going in C2


for Sheet 1

=SUMPRODUCT((Sheet1!$A$2:$A$366>=DATE(YEAR(Sheet1!$A$2),MONTH(C1),DAY(C1)))*(Sheet1!$A$2:$A$366<=EOMONTH(DATE(YEAR(Sheet1!$A$2),MONTH(C1),DAY(C1)),0)))

for Sheet 2

=SUMPRODUCT((Sheet2!$A$2:$A$366>=DATE(YEAR(Sheet2!$A$2),MONTH(C1),DAY(C1)))*(Sheet2!$A$2:$A$366<=EOMONTH(DATE(YEAR(Sheet2!$A$2),MONTH(C1),DAY(C1)),0)))
 
Thanks again Hui, but I get the same results regardless of what variation of the formula I use. Like I said the original formula works great for sheet1


but for sheet2 the formula(s) only pick up 9 of 20 records for Jan 2011.


And for the purpose of clarification I think the formula for sheet1 should be;


=SUMPRODUCT((Sheet1!A2:A366>=DATE(YEAR(Sheet1!A2),MONTH(A1),DAY(A1)))*(Sheet1!A2:A366<=EOMONTH(DATE(YEAR(Sheet1!A2),MONTH(A1),DAY(A1)),0)))


on sheet3 the criteria for 2010 starts in A1... I don't know what I'm doing wrong
 
OK Hui, this is the link to my skydrive. Hopefully you can get to it


https://cid-53e3aed6ab1594d6.office.live.com/view.aspx/Reserve%20Sheet%20Revision/Compare%20Test2%20modified%20for%20skydrive.xlsx
 
AZExcel


I am getting an error,

This folder might not be shared with you


Can you please check
 
OK try this link.. https://cid-53e3aed6ab1594d6.office.live.com/self.aspx/.QuickDocuments


The file is named; compare test2 modified for skydrive
 
Still not

It asks me to be your friend


My email is at the bottom of this page

http://chandoo.org/wp/about-hui/
 
On Sheet3

Under the Black date 2010 cells use

A2:=26 =SUMPRODUCT((Sheet1!$A$2:$A$329>=A1)*(Sheet1!$A$2:$A$329<=EOMONTH(A1,0)))

Copy from there to other cells


Under the Black date 2011 cells use

C2:=9 =SUMPRODUCT((Sheet2!$A$2:$A$37>=C1)*(Sheet2!$A$2:$A$37<=EOMONTH(C1,0)))

Copy from there to other cells


You will need to adjust the 2011 formulas as you add data to Sheet2
 
Hui,


You are one of the reasons why this is the best excel blogs on the planet. I appreciate your help with this problem. Thank you for sharing your expertise with me and the rest of the Chandoo blog community!!
 
You must log in or register to reply here.
Back
Top