Solved

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

Posted on 2011-03-19
12
230 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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
 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel IF formula 3 21
Excel format formula for currency 15 24
How best to represent data in Excel? 13 28
Formula Help 3 23
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

803 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