COUNTIFS - Data and dates between?

[radioactive]

Please bear with me on this one, everything I know about excel is self-taught so my knowledge is basic at best.

I have a spreadsheet which is used as a register for certain data. There are quite a few columns, but the relevant ones for my problem contain a 'BookedDate', 'Method' and 'ReportedDate'.

I've successfully written COUNTIFS formulas (using named ranges) into a separate worksheet for calculating the number of 'methods' booked and issued on a certain date, i.e. =COUNTIFS(Method,"XXXX",BookedDate,A7) with A7 being the certain date mentioned...

However, I also need to count the number of 'methods' that were not reported within a certain timeframe (the timeframe being the days elapsed between date booked and date reported. Let's say, one day for the first method.

I have tried writing this as follows (as well as a myriad of other ways, unsuccessfully!), =(COUNTIFS(Method,"XXXX",ReportedDate,A7,ReportedDate,">=1"&BookedDate))
I know I'm missing something somewhere. Or many things. Or maybe I'm over-complicating the whole thing. Perhaps what I'm trying to do is not even possible....
But if it is, I would greatly appreciate it if somebody could point me in the right direction! It seems simple enough...

To try and explain further, if I had 5 rows of the same method (XXXX) which were all booked on the 22/04/2014, where 4 methods were reported on the 23/04/2014 and one method was not reported until the 28/04/2014, then I would expect the formula to return '1'....if it helps to know, I need to calculate this for purposes of determining a 'backlog', so to speak.

I hope this makes sense!

Many thanks in advance

 

[Scrappy666]

Hi Radio,

The immediate thought would be the ">=1", i'm not sure what you're trying to do there.

=(COUNTIFS(Method,"XXXX",ReportedDate,A7,ReportedDate,">="&BookedDate))

I would try that for starters

 

[radioactive]

Hi Radio,The immediate thought would be the ">=1", i'm not sure what you're trying to do there.=(COUNTIFS(Method,"XXXX",ReportedDate,A7,ReportedDate,">="&BookedDate))I would try that for starters

Hi Scrappy, thanks for the reply! I actually have tried it exactly as you have suggested, but unfortunately the ">=1" is the important bit! I need it to only count methods which were not reported within an 'acceptable timeframe!' The actual timeframe differs for each method BUT, the fundamental thing I'm trying to achieve here is the same. For example, hypothetically lets say ">=1" was actually going to work; then for a certain method where the time between the date it was booked and the date it was recorded should not exceed 3 days, then I would amend the formula to ">=3". Obviously that is wrong, but that is the gist of what I'm trying to do!I don't suppose you have any other ideas? Although I do thank you for your contribution - it is very much appreciated

 

[FormR]

To try and explain further, if I had 5 rows of the same method (XXXX) which were all booked on the 22/04/2014, where 4 methods were reported on the 23/04/2014 and one method was not reported until the 28/04/2014, then I would expect the formula to return '1'...

Something like this maybe?

Excel Workbook
	A	B	C
7	22/04/2014	1	*
8	*	*	*
9	Method	booked	reported
10	XXXX	22/04/2014	23/04/2014
11	XXXX	22/04/2014	23/04/2014
12	XXXX	22/04/2014	23/04/2014
13	XXXX	22/04/2014	28/04/2014
Sheet1

 

[radioactive]

Something like this maybe?

Sheet1

*	A	B	C
*
*	*	*
Method	booked	reported
XXXX
XXXX
XXXX
XXXX

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:92px;"><col style="width:95px;"><col style="width:89px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]22/04/2014[/TD]
[TD="bgcolor: #ffff00, align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]22/04/2014[/TD]
[TD="align: right"]23/04/2014[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]22/04/2014[/TD]
[TD="align: right"]23/04/2014[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]22/04/2014[/TD]
[TD="align: right"]23/04/2014[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]22/04/2014[/TD]
[TD="align: right"]28/04/2014[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B7 =SUMPRODUCT(--(A10:A13="XXXX"),--(B10:B13=A7),--(C10:C13-B10:B13>1)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hi FormR,

That does indeed work as you have written it, but it doesn't seem to work when I input my named ranges. Is this where I am going wrong? My spreadsheet is a live document with well over 17000 rows so named ranges seemed to be the best way to calculate without having to manually update the formulas each time data is added...However, if this is not possible then your solution will suffice for the purpose of finding the information I need, so thank you very much

 

[FormR]

Hi RA,

Do you get an error or a wrong result when you substitute in the named ranges?

It should work with named ranges, but each of the named ranges must contain the same number of rows.

 

[radioactive]

Hello and apologies for the extremely belated response!! I have been away on leave for the past two weeks.

My named ranges are recorded like this: ='Register 2014'!$C:$C

So, it's not as though the problem is that Range 1 = A1:A5 and Range 2 = A2:A5 (if I am understanding you correctly)..

Some of the rows will be incomplete for an indefinite period (i.e. blank until the register is updated), could this be the problem?

Thanks again for your help,

 

[Skybluekid]

Are your named ranges dynamic? So as your data grows it, the named ranges grow as well.

 

[FormR]

My named ranges are recorded like this: ='Register 2014'!$C:$C

It's not a great idea to use full column references, especially with a sumproduct() formula. But I think the main issue is if you have headers in row 1 then this part --(C:C-B:B>1) will result in a #VALUE error.

Look up creating dynamic named ranges or use something like this, adjusting the rows to a sensible maximum:
=SUMPRODUCT(--(A2:A100000="XXXX"),--(B2:B100000=E1),--(C2:C100000-B2:B100000>1))

Excel Workbook
	A	B	C	D	E	F
1	Method	booked	reported		22/04/2014	1
2	XXXX	22/04/2014	23/04/2014
3	XXXX	22/04/2014	23/04/2014
4	XXXX	22/04/2014	23/04/2014
5	XXXX	22/04/2014	28/04/2014
Sheet1

 

[radioactive]

Adjusting the rows to a sensible maximum seemed like the simple and effective solution that had been staring me in the face all along. Alas! It is STILL not working for me! I'm still getting the #VALUE! error

I copied your formula exactly as you had it below (at least, I think I did) and also tried a few other variations that seemed possible but I just cannot wrap my head around this one. No doubt it's my lack of competence as it's working for you and you clearly have a firm grasp on these excel concepts!

This is the formula you have given me as translated into my document;

=SUMPRODUCT(--(I6:I100000="5.2.1"),--(C6:C100000=F17561),--(P6:P100000-C6:C100000>1))

I is my Method column, C is my Booked Date column, P is my Reported Date column and F17561 is my equivalent of E1 below. Can you see where I might be going wrong? I'm totally baffled.

Thanks in advance for your ongoing assistance and patience!

It's not a great idea to use full column references, especially with a sumproduct() formula. But I think the main issue is if you have headers in row 1 then this part --(C:C-B:B>1) will result in a #VALUE error.

Look up creating dynamic named ranges or use something like this, adjusting the rows to a sensible maximum:
=SUMPRODUCT(--(A2:A100000="XXXX"),--(B2:B100000=E1),--(C2:C100000-B2:B100000>1))

Sheet1

*	A	B	C	D	E	F
Method	booked	reported	*
XXXX	*	*	*
XXXX	*	*	*
XXXX	*	*	*
XXXX	*	*	*

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]22/04/2014[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]22/04/2014[/TD]
[TD="align: right"]23/04/2014[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]22/04/2014[/TD]
[TD="align: right"]23/04/2014[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]22/04/2014[/TD]
[TD="align: right"]23/04/2014[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]22/04/2014[/TD]
[TD="align: right"]28/04/2014[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula F1 =SUMPRODUCT(--(A2:A100000="XXXX"),--(B2:B100000=E1),--(C2:C100000-B2:B100000>1)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4