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

>>>count number of events per date

Use sumproduct()

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

Sid

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

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

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

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

Barry, I tested both Sumproduct and Countifs and they work great.  No conversion needed.

Dave

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

Precise and accurate, could not ask for more!

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

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

gotcha - thanks...