SMALL function with multiple IF statements (or alternative suggestion)

[garymon]

Hi, I am new to this forum and this is my 1st post for help.
I have outlined the problem below:

	A	B	C	D	E	F	G	H	I	J	K
1	Job number	visit date	Job type	Status			1st	2nd	3rd	4th	5th
2	23502-3	04/04/2017	Contract	Visit completed			1	2	3	4	5
3	23502-4	13/04/2017	Contract	Visit completed		23502
4	23502-5	07/06/2017	Contract	Visit completed		23509
5	23502-6	12/07/2017	Contract	Visit completed
6	23502-7	29/08/2017	Contract	Visit completed		For each 'job number' entered in column 'F', I want to insert the 1st, 2nd, 3rd etc occurrences of 'visit date' (Column 'B'), that relates to 'Job numbers' in column 'A', into cells under columns 'G3:K4'. However, only if they match the 'Job Type' "Contract" (Column 'C') AND the 'Status' "Visit Completed" (Column 'D'). I have tried the function 'SMALL' withmultiple 'IF' statements, but can't get it to work.
7	23502-8	11/10/2017	Contract	Visit completed
8	s23502-8(1)	02/11/2017	Rodents	Visit completed
9	23502-9	21/11/2017	Contract	Contract Routine
10	23502-10	18/01/2018	Contract	Visit completed
11	23509-3	04/04/2017	Contract	Visit completed
12	23509-4	13/04/2017	Contract	Visit completed
13	23509-5	07/06/2017	Contract	Visit completed
14	23509-6	12/07/2017	Contract	Visit completed
15	23509-7	29/08/2017	Contract	Visit completed

<tbody>
</tbody>

This example is an extract of a larger spreadsheet with much more data variations in the descriptions in columns C and D.

If someone could give me the formula I need to enter in cells G3:K4, that gives me the date occurrences I need, I would be most grateful.

Forgive me if I have not got the forum etiquette or format correct. I am new to this. Any advice would be welcome.
Thank you.

 

[Nishant94]

If you have data like this, you can use this:

Excel 2016
	A	B	C	D
1	Job number	visit date	Job type	Status
2	23502-3	04/04/2017	Contract	Visit completed
3	23502-4	13/04/2017	Contract	Visit completed
4	23502-5	07/06/2017	Contract	Visit completed
5	23502-6	12/07/2017	Contract	Visit completed
6	23502-7	29/08/2017	Contract	Visit completed
7	23502-8	11/10/2017	Contract	Visit completed
8	s23502-8(1)	02/11/2017	Rodents	Visit completed
9	23502-9	21/11/2017	Contract	Contract Routine
10	23502-10	18/01/2018	Contract	Visit completed
11	23509-3	04/04/2017	Contract	Visit completed
12	23509-4	13/04/2017	Contract	Visit completed
13	23509-5	07/06/2017	Contract	Visit completed
14	23509-6	12/07/2017	Contract	Visit completed
15	23509-7	29/08/2017	Contract	Visit completed
15

Unknown
	F	G	H	I	J	K	L	M	N	O
1		1st	2nd	3rd	4th	5th
2		1	2	3	4	5	6	7	8	9
3	23502			04/04/2017	13/04/2017	07/06/2017	12/07/2017	29/08/2017	11/10/2017
4	23509			04/04/2017	13/04/2017	07/06/2017	12/07/2017	29/08/2017
5
6
7
8
9
10
15

Cell Formulas
Range	Formula
G3	=SUMIFS($B$2:$B$15,$A$2:$A$15,$F3&"-"&G$2,$C$2:$C$15,"Contract",$D$2:$D$15,"Visit Completed")

Copy the formula down and across and use custom format with this: dd/mm/yyyy;"";"";@

 

[garymon]

Hi Nishant94,

Thanks for your prompt reply and suggestion.
I cant't quite get it to work as I want, because your formula returns nothing in the 1st and 2nd occurrence columns (G-H).
I don't fully understand the part of your formula '$F3&"-"&G$2'. It seems to look for the suffix part of the numbers in column 'A'. e.g. because the 1st occurrence of the Job number 23502 has the suffix "- 3", your formula returns the date in the 3rd occurrence (column I). However, I need it to ignore the suffixes and return the 1st date occurrence of the job number into cell 'G3' as the 1st occurrence, regardless of suffix number.

I hope that makes sense.

Thank you for your help.

Regards,

garymon

If you have data like this, you can use this:

Excel 2016

	A	B	C	D
1	Job number	visit date	Job type	Status
2	23502-3	04/04/2017	Contract	Visit completed
3	23502-4	13/04/2017	Contract	Visit completed
4	23502-5	07/06/2017	Contract	Visit completed
5	23502-6	12/07/2017	Contract	Visit completed
6	23502-7	29/08/2017	Contract	Visit completed
7	23502-8	11/10/2017	Contract	Visit completed
8	s23502-8(1)	02/11/2017	Rodents	Visit completed
9	23502-9	21/11/2017	Contract	Contract Routine
10	23502-10	18/01/2018	Contract	Visit completed
11	23509-3	04/04/2017	Contract	Visit completed
12	23509-4	13/04/2017	Contract	Visit completed
13	23509-5	07/06/2017	Contract	Visit completed
14	23509-6	12/07/2017	Contract	Visit completed
15	23509-7	29/08/2017	Contract	Visit completed

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

15​

Unknown

	F	G	H	I	J	K	L	M	N	O
1		1st	2nd	3rd	4th	5th
2		1	2	3	4	5	6	7	8	9
3	23502			04/04/2017	13/04/2017	07/06/2017	12/07/2017	29/08/2017	11/10/2017
4	23509			04/04/2017	13/04/2017	07/06/2017	12/07/2017	29/08/2017
5
6
7
8
9
10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

15​

Worksheet Formulas Cell Formula G3 =SUMIFS($B$2:$B$15,$A$2:$A$15,$F3&"-"&G$2,$C$2:$C$15,"Contract",$D$2:$D$15,"Visit Completed") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Copy the formula down and across and use custom format with this: dd/mm/yyyy;"";"";@