Countifs and comparing dates

[jontyoc]

Hi All

I am wanting to count the number of cases we opened and closed the same day.
My countif works for the closed part of the sheet, but struggling with the addition of days into the equation.

Columns E and F on Daily Import sheet are the date but formatted from the data pull but dd/mm/yyyy hh:mm. I had thought to use LEFT(E:E,10) as this will pull only date format.

The formula I have currently is this:

=COUNTIFS('Daily Import'!I:I,"closed", LEFT('Daily Import'!F:F,10), "=" & LEFT('Daily Import'!E:E,10))

but I am getting the Excel problem with formula beep.
I have tried

=COUNTIFS('Daily Import'!I:I,"closed", ('Daily Import'!F:F), LEFT('Daily Import'!E:E,10)= LEFT('Daily Import'!F:F,10))

This at least returns a value of 0, when it should be 25.

So how can I amend my formula to count the cases that are closed on the same day they are opened????


Thanks.

 

[Joe4]

It is important to understand how Excel stores dates. Specifically, it is the number of days since 1/0/1900. And time is stored as the fraction of one day.
So if we entered 9:00 AM for the current date, and changed the format to "General" or "Number", we would see it as Excel does (43350.375).

So, if you wanted to drop the time component off of a date, one way is to use the INT function, which drops the decimal (time) component.

Note that the SUMIFS/COUNTIFS formulas often do not like the use of functions in the Criteria portion, in which case, you may need to use SUMPRODUCT instead, i.e.

=SUMPRODUCT(--('Daily Import'!I:I="closed"),--(INT('Daily Import'!E:E)=INT('Daily Import'!F:F)))

 

[jontyoc]

Hi Joe

Thanks for this. I did think it was something to do with date format Back at work now and trying this formula and it is still giving me #VALUE error.

 

[Joe4]

Do you have a header or other text in columns E and/or F?
If so, you will need to exclude the row. So instead of using whole column references, use defined ranges, i.e.

=SUMPRODUCT(--('Daily Import'!I2:I1000="closed"),--(INT('Daily Import'!E2:E1000)=INT('Daily Import'!F2:F1000)))

 

[jontyoc]

Delightful. Thanks very much for your time and help Joe!

 

[Joe4]

You are welcome!

 

[jontyoc]

Is it possible for me to move this formula to look at a column (G) that also may have (NONE) in rather than date/number value?
If so how will I amend the formula?

 

[Joe4]

Is it possible for me to move this formula to look at a column (G) that also may have (NONE) in rather than date/number value?

I do not clear on what you are asking.
Is a certain part of the existing formula moving to column G, or is this an additional requirement/criteria?
Maybe it would be better to lay out some simple examples for us, along with your expected results.

 

[jontyoc]

Sorry what I am wanting to do is now look at column G.
Part of the formula would be moving to G, or changing it to compare column G with cell K10 (todays date)
After a little digging around I thought this would work but still getting

=SUMPRODUCT(--('Daily Import'!G3:G5000<>"(None)"),--('Daily Import'!$I3:I5000,)="closed"),--(INT('Daily Import'!G3:G5000)=INT($K$10))

but I still get #VALUE error. (Excel keeps removing the , from end of second array.)

I have also tried

=SUMPRODUCT(--(ISNUMBER('Daily Import'!g3:g5000),--('Daily Import'!$i3:i5000)="closed"),--(INT('Daily Import'!g3:g5000))=INT(K10)))

but still getting same.
I want to get the following to happen from the below data:

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E (Created)[/TD]
[TD]F(raised)[/TD]
[TD]G (closed)[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]19/9/2018 19:56[/TD]
[TD][/TD]
[TD]closed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](None)[/TD]
[TD][/TD]
[TD]closed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](None)[/TD]
[TD][/TD]
[TD]open[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20/9/2018 12:47[/TD]
[TD][/TD]
[TD]closed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20/9/2018 11:35[/TD]
[TD][/TD]
[TD]closed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](None)[/TD]
[TD][/TD]
[TD]open[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](None)[/TD]
[TD][/TD]
[TD]open[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](None)[/TD]
[TD][/TD]
[TD]open[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]19/9/2018 16:50[/TD]
[TD][/TD]
[TD]closed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20/9/2018 15:30[/TD]
[TD][/TD]
[TD]closed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20/9/2018 12:30[/TD]
[TD][/TD]
[TD]closed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20/9/2018 11:26[/TD]
[TD][/TD]
[TD]closed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](None)[/TD]
[TD][/TD]
[TD]open[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](None)[/TD]
[TD][/TD]
[TD]open[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](None)[/TD]
[TD][/TD]
[TD]open[/TD]
[/TR]
</tbody>[/TABLE]

























From this data, I want to know how many are closed on a particular day.
I can get the formula to work on number of closed ignoring the (None), but then want to know how many are closed on 20/9 or any other date (K10)

In this case 6 closed 20th, and 7 closed ignoring (None) with 8 in total.
The rest of the data is not useable for what is needed. All text except E & F which are dates, but need to know when closed not created/raised.
Hope this helps.

 

[Joe4]

You have some parentheses in the wrong place.
Try this:

=SUMPRODUCT(--(ISNUMBER('Daily Import'!G3:G5000)),--('Daily Import'!$I3:I5000="closed"),--(INT('Daily Import'!G3:G5000)=INT(K10)))