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.

 

[FDibbins]

What is in C1?
Are you sure all data is dates? test with =ISNUMBER(cell-ref)
This should work if all data matches...
=COUNTIFS(owssvr!B2:B942,C1,owssvr!S2:S942,"pass")

Something else, does it work if you remove the "pass" criteria? or the Date criteria?

 

[Aladin Akyurek]

If this works...

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

so would

If C1 = 4/1/2016...

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

should work.

What result did you get?

 

[isadoko]

First C1 holds my date -- first day of each month because I meant it to summarize by month. I surrendered and added a second date field, D1, featuring the last day of the month and referenced the original date and time in column A so that my formula now shows as and works except that it will not count the blanks, ie, "", and unsure the why. One additional glitch I need to move from column to column and have not figured out how to do that. The columns I need use range from S to BG. Would that entail adding a HLOOKUP? Thanks all.

=COUNTIFS(owssvr!A2:A942,">="&$C$1,owssvr!A2:A942,"<="&$D$1,owssvr!S2:S942,"pass")

 

[Aladin Akyurek]

Does owssvr!A2:A942 consist of first day dates or date/time values?

 

[isadoko]

Date and time for export from SharePoint...12/23/2016 1:42:53 PM

 

[Tetra201]

First C1 holds my date -- first day of each month because I meant it to summarize by month. I surrendered and added a second date field, D1, featuring the last day of the month and referenced the original date and time in column A so that my formula now shows as and works except that it will not count the blanks, ie, "", and unsure the why. One additional glitch I need to move from column to column and have not figured out how to do that. The columns I need use range from S to BG. Would that entail adding a HLOOKUP? Thanks all.

=COUNTIFS(owssvr!A2:A942,">="&$C$1,owssvr!A2:A942,"<="&$D$1,owssvr!S2:S942,"pass")

You don't have to convert date-and-time values to dates only and you don't have to use D1 -- the following formula would work:

=COUNTIFS(owssvr!A2:A942,">="&$C$1,owssvr!A2:A942,"<"&EOMONTH($C$1,0)+1,owssvr!S2:S942,"pass")

As for counting blanks and moving from column to column, could you please elaborate?

 

[FDibbins]

If your cells contain data like you said...
12/23/2016 1:42:53 PM
then looking for =12/23/2016 will not find any matches - you are looking for an exact number, but 12/23/2016 1:42:53 PM actually contains decimals

What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Thu 29 Dec 2016) is actually 42733

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

so 12/23/2016 6:00 AM is actually 42727.25
Looking for exactly 12/23/2016 will result in nothing being found, because of the decimal

 

[Aladin Akyurek]

Date and time for export from SharePoint...12/23/2016 1:42:53 PM

Thanks. Since C1 is a first day date or a date from the month/year of interest...

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

 

[isadoko]

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.