Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-03-19
12
Medium Priority
?
238 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 42

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 42

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

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 42

Expert Comment

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

Dave
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 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 42

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 42

Expert Comment

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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
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…

886 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