Countif only one per row

[janarthenan]

Hi All

I have been trying to achieve the following for days now and can't seem to figure out a way.

I have a spreadsheet with dates when a supervision is done for a particular staff.

I am trying to have a weekly report so that I know on that particular week what my supervision Percentage was.

Every staff needs their supervision done at least every 3 months.

When I do the COUNTIF and if some one had their supervision twice (one in April and one in June) on the June calculation it counts it as 2.

Name	April	May	June	July	August	Sept	Oct	Nov	Dec	Last Supervision	Next Supervision
A	1/4/17		1/6/17	1/7/17						=MAX(B2:J2)	=H2+90
B		1/5/17			1/8/17					=MAX(B3:J3)	=H3+90
C										..	..
D
E
F

<tbody>
</tbody>

When I do COUNTIF(B2:J2, "<="&1/4/17) - I get 1
When I do COUNTIF(B2:J2, "<="&1/7/17) - I get 3 (Which is correct) but I want this to be one.
- So basically I want the calculation to count the highest value only per row and If that highest value meets the COUNTIF condition to come back as 1.

Prefer if no VBA was used.

Regards
J

 

[Peter_SSs]

I'm not at all clear on what you want but if the following is true ..

When I do COUNTIF(B2:J2, "<="&1/7/17) - I get 3 (Which is correct) but I want this to be one.

.. then does this do what you want?
MIN(1, COUNTIF(B2:J2, "<="&1/7/17))

 

[janarthenan]

Hi Peter
Thanks for your quick reply.

I am sorry, its a little difficult to explain as I am unable to attach a file.

I might have not explained myself clearly there.
My Formula is actually (B2:F3) It covers a range rather than a single row.
So When I Do When I do COUNTIF(B2:F2, "<="&1/7/17) - The answer I get is 4. But i want it to be 2.
So it should if a row contains two values then only the highest value should be counted.
On a row by row basis this is achievable but when the whole range is selected it seems impossible.

Jan

 

[Peter_SSs]

You cannot attach files but you can post small screen shots that can be copied (see my signature block below for a link), or even a table like you did above (but tell us what the rows and columns are).

Instead of telling us what formula you are trying to use, show us the results that you want (manually entered) on that screen shot and then explain in words how you manually come up with the results. Then we will see if we can devise a formula to do it.

It might also clarify better if you give us a few more than 2 rows of data. Say 5 or 6.

 

[janarthenan]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	Supervisee	Last Supervision from Prev.	Apr - 17	May - 17	Jun - 17	Jul - 17	Aug - 17	Sep - 17	Oct - 17	Nov - 17	Dec - 17	Jan - 18	Feb - 18	Mar - 18	Last Supervision Date	Next Supervision
3	A	22/11/2016		05/05/2017											05/05/2017	03/08/2017
4	B	21/02/2017	01/04/2017		02/06/2017										02/06/2017	31/08/2017
5	C	14/11/2016		10/05/2017		19/07/2017									19/07/2017	17/10/2017
6	D	09/03/2017		21/05/2017											21/05/2017	19/08/2017
7	E	17/02/2017	11/04/2017		04/06/2017										04/06/2017	02/09/2017
8	F	10/07/2017		02/05/2017											10/07/2017	08/10/2017
9	G	21/01/2017	01/04/2017		10/06/2017		03/08/2017								03/08/2017	01/11/2017
10	H	10/09/2016		23/05/2017											23/05/2017	21/08/2017
Supervision (2)

This is the Raw Data Table:

The Below is the Calculation Table:

Book1
	D	E	F	G
13	Week Ending	Supervision	Value Should be	How (Value Should Be is Calculated)
14	08/04/2017	6.00	3.00	The formula should count all dates in the range (B3:N10) that fall between Week Ending (Column D) and Week Ending - 90 Days. If there are 2 values in a row then it should only count the highest value. For Example - Supervisee B has 2 Dates (21/02/17 and 01/04/17) but the lowest date should be ignored and should be counted as 1.
15	22/04/2017	6.00	4.00
16	06/05/2017	8.00	5.00
17	20/05/2017	8.00	6.00
18	03/06/2017	10.00	8.00
19	17/06/2017	11.00	8.00
20	01/07/2017	9.00	8.00
21	15/07/2017	9.00	8.00
Supervision (2)

Thanks for all the Help

Jan

 

[Peter_SSs]

Sorry, I still don't understand. (But thanks for the images where we can see what rows and columns the data is in)

For one thing, you said "For Example - Supervisee B has 2 Dates (21/02/17 and 01/04/17)", but when I look at Supervisee B I see 3 dates, the 2 you mentioned but also 02/06/2017. Please clarify.

Could explain exactly how you came up with a count of 3 for cell F14 in the 2nd image, including which cells from the top image were counted and why?

 

[janarthenan]

Hi Peter

Let me explain the Row 14.
The week ending date is 08/04/17.
My criteria should count any dates between 08/01/17 (90 dates before the week ending date) to 08/04/17.
So the value counted 6.

But In the example of Supervisee B there are 2 dates that fall in this criteria. (21/02/17 and 01/04/17)
But I only need the biggest date to be counted i.e. 01/04/17.

In Summary if there are two dates in a row that satisfy the criteria (i.e week ending date to week ending date-90) then the highest date should be counted and the rest ignored.

Thanks
Jan

 

[Peter_SSs]

Let me explain the Row 14.
The week ending date is 08/04/17.
My criteria should count any dates between 08/01/17 (90 dates before the week ending date) to 08/04/17.
So the value counted 6.

Ok, I can see the count of 6 (coloured cells below) but earlier you said ...

If there are 2 values in a row then it should only count the highest value.

.. so to me that should eliminate the 2 blue cells leaving a count of 4 (green). How do you arrive at the expected value of 3 as shown in post 5?

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G
2	Supervisee	Last Supervision from Prev.	Apr-17	May-17	Jun-17	Jul-17	Aug-17
3	A	22/11/2016		5/05/2017
4	B	21/02/2017	1/04/2017		2/06/2017
5	C	14/11/2016		10/05/2017		19/07/2017
6	D	9/03/2017		21/05/2017
7	E	17/02/2017	11/04/2017		4/06/2017
8	F	10/07/2017		2/05/2017
9	G	21/01/2017	1/04/2017		10/06/2017		3/08/2017
10	H	10/09/2016		23/05/2017
Count

 

[janarthenan]

Peter

Apologies, You are right, it should be 4, I missed out one of the dates.

Thats exactly what I am trying to achieve.

Jan

 

[Peter_SSs]

I know you said you would prefer without vba but so far I have not been able to come up with a formula to do the job. There likely is one and perhaps somebody else will be able to post that for you.
Until then, you might consider the vba approach via a user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Note: You referred to checking back 90 days. My function should do that, but if you actually wanted to check back 3 months instead, use the other 'EarlierDate' line in the code that is currently commented out.

Function Count_Rows(RngToLookIn As Range, DateToCheck As Date) As Long
  Dim aRangeToLookIn As Variant
  Dim EarlierDate As Date
  Dim i As Long, j As Long
  
  aRangeToLookIn = RngToLookIn.Value
  EarlierDate = DateAdd("d", -90, DateToCheck)
  'EarlierDate = DateAdd("m", -3, DateToCheck)
  For i = 1 To UBound(aRangeToLookIn, 1)
    For j = 1 To UBound(aRangeToLookIn, 2)
      If aRangeToLookIn(i, j) <= DateToCheck Then
        If aRangeToLookIn(i, j) >= EarlierDate Then
          Count_Rows = Count_Rows + 1
          Exit For
        End If
      End If
    Next j
  Next i
End Function

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N
2	Last Supervision from Prev.	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18
3	22/11/2016		5/05/2017
4	21/02/2017	1/04/2017		2/06/2017
5	14/11/2016		10/05/2017		19/07/2017
6	9/03/2017		21/05/2017
7	17/02/2017	11/04/2017		4/06/2017
8	10/07/2017		2/05/2017
9	21/01/2017	1/04/2017		10/06/2017		3/08/2017
10	10/09/2016		23/05/2017
11
12
13			Week Ending	Supervision	Count
14			8/04/2017	6	4
15			22/04/2017	6	4
16			6/05/2017	8	6
17			20/05/2017	8	7
18			3/06/2017	10	8
19			17/06/2017	11	8
20			1/07/2017	9	8
Supervision

Cell Formulas
Range	Formula
F14	=Count_Rows(B$3:N$10,D14)