# Countifs & Date in Another Field

##### Active Member
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.

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### FDibbins

##### Well-known Member
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?

##### MrExcel MVP
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?

##### Active Member
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")

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

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

#### Tetra201

##### MrExcel MVP
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?

Last edited:

#### FDibbins

##### Well-known Member
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

##### MrExcel MVP
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")

##### Active Member
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.

Replies
8
Views
137
Replies
3
Views
623
Replies
3
Views
482
Replies
2
Views
254
Replies
1
Views
148

1,191,050
Messages
5,984,370
Members
439,883
Latest member
onions44

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back