Use COUNTIFS function on results of DATEVALUE function?

Hi all

Thanks in advance.

I have taken over a workbook with 1800 rows of data, it is populated with the day, month & year in columns D,E & F, then in column B the DATEVALUE function has been used to combine the three into a standard format date DD/MM/YYYY, the also in column A the WEEKDAY function has been used to calculate the day of the week.

Now i need to produce some stats using the COUNTIFS function based on data between two dates, i've been trying to use the dates from column B (the DATEVALUE results) but cant get it to work.

Is there any way of converting it / make it work without re inputting 1800 dates manually?

Please find a sample attached.

Regards

SiSample.xlsx

This will count how many dates exist between the 2 dates in sheet1
=COUNTIFS(Prisoners!B:B,">="&B1,Prisoners!B:B,"<="&B2)

Originally Posted by mcmahobt

What exactly are you trying to count? Is it the number of D-I/D (Assist or Direct) between the start and stop dates on Sheet1?

Originally Posted by Special-K

I see a worksheet.
I see it has parts of dates in columns D E F
I see they are combined in column B.

I see in your description you say you have been using COUNTIFs to produce "some stats".
But I dont see a COUNTIF anywhere in that worksheet.

So you'll need to explain further what you're trying to achieve.

Originally Posted by Jonmo1

This will count how many dates exist between the 2 dates in sheet1
=COUNTIFS(Prisoners!B:B,">="&B1,Prisoners!B:B,"<="&B2)

Originally Posted by MarvinP

Hi Si,

First you will need to find the bad dates and missing dates in your data so each has a valid date. Then I assume you want some kind of counts per the between dates. See the attached Pivot Table where I've done that using Excel 2013. I believe it is also possible in 2007 but think you can do it. ALSO - you will need to add column headers to each column so Excel can deal with your data as a table. Duplicate head names or missing head names aren't allowed. See the attached and start looking at Pivots for a possible answer.

Thanks all for your responses.

Sorry i should have realised you need some indication of what im trying to achieve, I've re attached a sample.

On sheet 2 row 5 columns B:G i have put in the functions / formulas i have tried to use to no avail.

I need to count the number of times a name exists in sheet1 G:H based on the dates entered in Sheet2 B1 & B2.

Also, the same including if the criteria Direct / Assist / Pro Active appears against the name, and Front or Back.

Sorry wouldn't let me attach for some reason.


Sample 1.xlsx

Try this

In C5 and filled accross/down to E11
=SUMPRODUCT(('Sheet 1'!$G$2:$H$48=$A5)*('Sheet 1'!$L$2:$L$48=C$4)*('Sheet 1'!$B$2:$B$48>=$B$1)*('Sheet 1'!$B$2:$B$48<=$B$2))

In B5 and filled down
=SUM(C5:E5)

Thanks mcmahobt but doesn't seem to work either, just returns 0.

Not sure why the DATEVALUE results would prevent counting between dates, or am i missing something?

Originally Posted by Jonmo1

This will count how many dates exist between the 2 dates in sheet1
=COUNTIFS(Prisoners!B:B,">="&B1,Prisoners!B:B,"<="&B2)

Thanks Jonmo1

I get that far and succesfully count the dates, however as soon as i enter another Range and criteria to count it returns 0.

i.e count all entries between the two dates as above, but only if the name in Sheet2 Cell A5 appears in Sheet1 G:H.

Then i need to add further criteria to the above, i.e count the number of times a name appears in Sheet1 G:H between the two dates with an entry "Direct" against it in Sheet1 L:L

Please see new attachment "Sample1" at post #7.

I know this seems quite simple in theory but i just can't get it to work.

All your help is much appreciated

Thank you.