Using Sumproduct to Count Dates Ignoring Blanks

[CamBoss125]

Alright it's a little convoluted but stick with me.

I'm working on a motorsports spreadsheet and am trying to count the number of times a race has been held on a date (not including year, so for example how many times there was a race on May 5th).

The formula I have currently is =SUMPRODUCT(--(MONTH($E:$E<>0)=5),--(DAY($E:$E<>0)=1)) and returns 0, which I know to be untrue thanks to a PivotTable I've made that groups the dates in month-day form for me. Adding the range as the first and fourth arrays (=SUMPRODUCT($E:$E,--(MONTH($E:$E<>0)=5),$E:$E,--(DAY($E:$E<>0)=1))) also returns 0, and changing <>0 to <>"" does nothing to the value.

(The reason I'm doing this to begin with is to have the data organized in a calendar-style format)

I'd like to keep the range the entire column of E, so I don't have to continuously update the formula 366 times per race, and I can just input the date of the next race and the calendar update itself. Of course this means I'd like the formula to disclude blanks.

What in the formula is causing the error, and how do I fix it?

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

[TMS]

Try, for example,

Please Login or Register  to view this content.

Don't use full column references with SUMPRODUCT. It will adversely affect performance of the workbook.

With 365, you could use something like:

Please Login or Register  to view this content.

[AliGW]

I'm not sure that the 'dates' are really dates - still need to see a workbook, if TMS' solution does not work.

[CamBoss125]

Sample.xlsx

Here's some data used in the sheet with the expected look of the calendar done manually.

[JeteMc]

Not the same setup but the same values.
The formula used is:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Gregb11]

You could try in D3:

=LET(rng,$A$1:$A$1000,a,FILTER(rng,(MONTH(rng)=MONTH(D2))*(DAY(rng)=DAY(D2)),0),IF(@a=0,0,ROWS(a)))

copied across and then down to each row where needed.

[bebo021999]

D3:

Please Login or Register  to view this content.