Countifs & Date in Another Field

[isadoko]

I have a column of dates with time. I have converted the date and time to date only using =INT(A2); the result is in column B. in C1 I created a drop-down with the months all based on the first day of the month, ie, 1/1/2016, 2/1/2016, 3/1/2016, etc. I want to use the drop down to trigger the =COUNTIFS like this:

=COUNTIFS(owssvr!B2:B942,C1,owssvr!S2:S942,"pass")... also tried =COUNTIFS(owssvr!B2:B942,"="&C1,owssvr!S2:S942,"pass") unsuccessfully.

So far does not want to acknowledge the date in cell C1 so have had to write like this:

=COUNTIFS(owssvr!B2:B942,">=4/1/2016",owssvr!B2:B942,"<=5/1/2016",owssvr!S2:S942,"pass")

Obviously doing something wrong but cannot put finger on it. Would appreciate any help to find my way. Thanks.

 

[Tetra201]

Tried your edit and works nicely. As for the blanks now working. Finally, I would like drag down and across if possible so need columnar reference to move from left to right from column S, start of data set of interest to column BG the last column of dataset of interest. I have read about HLOOKUP and SUM(INDEX(MATCH and OFFSET(MATCH but as yet not been able to realize a workable solution.

The following formula will allow dragging across and down; see if it works for you:

=COUNTIFS(owssvr!$A:$A,">="&$C$1,owssvr!$A:$A,"<"&EOMONTH($C$1,0)+1,owssvr!S:S,"pass")

 

[Aladin Akyurek]

Tried your edit and works nicely. As for the blanks now working. Finally, I would like drag down and across if possible so need columnar reference to move from left to right from column S, start of data set of interest to column BG the last column of dataset of interest. I have read about HLOOKUP and SUM(INDEX(MATCH and OFFSET(MATCH but as yet not been able to realize a workable solution.

Are you intending something like this which can be copied to the right and down?

=COUNTIFS(owssvr!$A:$A,">="&$C$1,owssvr!$A:$A,"<"&EOMONTH($C$1,0)+1,INDEX(owssvr!$S:$XFD,0,COLUMNS($S:S)),"pass")

 

[isadoko]

I see how you incorporated the INDEX and COLUMNS... What does XFD do?

 

[Aladin Akyurek]

I see how you incorporated the INDEX and COLUMNS... What does XFD do?

I took S to XFD as the set of columns from which to choose. If BG:BG is the last possible column, we can replace XFD with BG.

 

[isadoko]

This has been most helpful and now to apply it--thank you and best wishes 2017.

 

[Aladin Akyurek]

This has been most helpful and now to apply it--thank you and best wishes 2017.

You are welcome. And likewise.