Solved

Excel: Count events per date in an array with date and time values

Posted on 2011-03-19
12
229 Views
Last Modified: 2012-05-11
Hi - I have an array of records, each with date and time, and I want to count number of events per date excluding the time. What is a best way to do it?
I tried "SumIf" but it did not work...

SUM(IF(AND(DAY($B$1:$B$20)=DAY($C$1),MONTH($B$1:$B$20)=MONTH($C$1),YEAR($B$1:$B$20)=YEAR($C$1)),1))

tks, N2V
0
Comment
Question by:NewToVBA
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35172064
>>>count number of events per date

Use sumproduct()

I can give you more details once I see the data.

Sid
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35172098
Yes, sumproduct would be better.  THe formula you have is inefficient - but it may work if you hit:

F2 to edit your formula, then CTRL-ALT-ENTER - this adds the curly braces to convert it to an array formula.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35172110
SUM(IF(AND(DAY($B$1:$B$20)=DAY($C$1),MONTH($B$1:$B$20)=MONTH($C$1),YEAR($B$1:$B$20)=YEAR($C$1)),1))


should be equivalent to:

=SUMPRODUCT(--(DAY($B$1:$B$20)=DAY($C$1))*(MONTH($B$1:$B$20)=MONTH($C$1))*(YEAR($B$1:$B$20)=YEAR($C$1)))

You don't need to hit CTRL-ALT-ENTER on this as SUMPRODUCT is already an array function.

Finally, you could also do something very similar with COUNTIFS as well, since it looks like you're counting (that's what the -- int he SUMPRODUCT does).

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35172117
So for counting, you could also use COUNTIFS.

Like this:

=COUNTIFS(DAY($B$1:$B$20),DAY($C$1),MONTH($B$1:$B$20),MONTH($C$1),YEAR($B$1:$B$20),YEAR($C$1))

Enjoy!

Dave

0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35172118
Typically you can't use AND in these formulas.....To get the Date form a Date/Time cell you can use INT so try

=SUMPRODUCT((INT(B$2:B$20)=$C$1)+0)

INT returns an error if it encounters text so B2:B20 need to be dates/times....

regards, barry
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35172122
Barry, I tested both Sumproduct and Countifs and they work great.  No conversion needed.

Dave
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 50

Accepted Solution

by:
barry houdini earned 125 total points
ID: 35172152
>No conversion needed

I'm not sure what you mean by "conversion", Dave?

That COUNTIFS formula doesn't work, you can't modify the ranges in COUNTIFS with YEAR or MONTH function etc.

To use COUNTIFS (if you have Excel 2007 or later) you can use this version

=COUNTIFS(B:B,">="&$C$1,B:B,"<"&$C$1+1)

I'm assuming that C1 is a date (no time)

regards, barry
0
 

Author Comment

by:NewToVBA
ID: 35172425
Genltemen, thank you all for you input. I tested all solutions and the only one that worked was given by Barry. I added a range name to his formula, just to make sure ...

I attached a test file, FYI. Testing-Formulas.xls
0
 

Author Closing Comment

by:NewToVBA
ID: 35172435
Precise and accurate, could not ask for more!
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35172492
Barry - i stand corrected on the COUNTIFS, which I thought I had adequately tested...  (corner of shame, here)
SUMPRODUCT however works like a charm.  I was curious with your comment "typically can't use an AND"...

Dave
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35172771
Hello Dave,

I was referring to the formula that N2V used in his question, i.e.

SUM(IF(AND(DAY($B$1:$B$20)=DAY($C$1),MONTH($B$1:$B$20)=MONTH($C$1),YEAR($B$1:$B$20)=YEAR($C$1)),1))

AND function doesn't work as needed in such formulas because it returns a single result not an array.....so you need to use * as an AND substitute (or multiple IFs). The closest working version to the posted would be this "array formula"

=SUM(IF((DAY($B$1:$B$20)=DAY($C$1))*(MONTH($B$1:$B$20)=MONTH($C$1))*(YEAR($B$1:$B$20)=YEAR($C$1)),1))

confirmed with CTRL+SHIFT+ENTER

Obviously, though, I recommend the shorter versions.....

regards, barry

0
 
LVL 41

Expert Comment

by:dlmille
ID: 35172802
gotcha - thanks...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now