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

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
NewToVBAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SiddharthRoutCommented:
>>>count number of events per date

Use sumproduct()

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

Sid
0
dlmilleCommented:
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
dlmilleCommented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

dlmilleCommented:
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
barry houdiniCommented:
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
dlmilleCommented:
Barry, I tested both Sumproduct and Countifs and they work great.  No conversion needed.

Dave
0
barry houdiniCommented:
>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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NewToVBAAuthor Commented:
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
NewToVBAAuthor Commented:
Precise and accurate, could not ask for more!
0
dlmilleCommented:
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
barry houdiniCommented:
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
dlmilleCommented:
gotcha - thanks...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.