Countifs criteria between two dates

[Si902]

Hi All

Im trying to use a COUNTIFS function to return the number of times a football team plays between two given dates.

I've tried various combinations but cannot get it to work!!

I'll attach a copy of the workbook, but basically i have Date in Column A, Home team Column B, Away team column C.

I have a cell with the start date (I2) and cell with end date (I3) to set the required period.

I have list of the teams in the league H5:H24, results for each team to be displayed in next column next to relevant team.

My last effort =COUNTIFS(B:C,H5,A:A,">= I2",A:A,"<= I3") results in #VALUE!

Not sure weather countifs is appropriate, maybe something better?

Any pointers in the right direction would be greatly appreciated.

Thanks in advance

[daddylonglegs]

All the ranges need to be the same size with COUNTIFS so to use that function you need to use it twice, once for each column, i.e. this formula in I5 copied down

=COUNTIFS(B:B,H5,A:A,">="&I$2,A:A,"<= "&I$3)+COUNTIFS(C:C,H5,A:A,">="&I$2,A:A,"<= "&I$3)

....or if you prefer to avoid the repetition you can use an "array formula" for the same result, i.e.

=SUM(IF(B$2:C$300=H5,IF(A$2:A$300>=I$2,IF(A$2:A$300<=I$3,1))))

confirmed with CTRL+SHIFT+ENTER

[Marcol]

Another possibility might be

Please Login or Register  to view this content.

[Si902]

Thanks daddylonglegs, i understand why it wouldnt work now.

Thanks Marcol, im sure it works very well but im no good with code, ill stick with the COUNTIFS function, thanks all the same!

[Marcol]

SUMPRODUCT() is a standard Excel function.

The forum doesn't have tags for formulae, and there are occassions when it strips and splits a formula unless some sort of tags are used.
Some members use code tags with native formula, some don't.

Sorry for the confussion.