SMALL function with multiple IF statements (or alternative suggestion)

G

garymon

New Member
Joined
Feb 4, 2018
Messages
2
Hi, I am new to this forum and this is my 1st post for help.
I have outlined the problem below:


[TABLE="width: 822"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Job number
[/TD]
[TD]visit date
[/TD]
[TD]Job type
[/TD]
[TD]Status
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]1st
[/TD]
[TD]2nd
[/TD]
[TD]3rd
[/TD]
[TD]4th
[/TD]
[TD]5th
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]23502-3
[/TD]
[TD]04/04/2017
[/TD]
[TD]Contract
[/TD]
[TD]Visit completed
[/TD]
[TD][/TD]
[TD]
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]23502-4
[/TD]
[TD]13/04/2017
[/TD]
[TD]Contract
[/TD]
[TD]Visit completed
[/TD]
[TD][/TD]
[TD]23502
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]23502-5
[/TD]
[TD]07/06/2017
[/TD]
[TD]Contract
[/TD]
[TD]Visit completed
[/TD]
[TD][/TD]
[TD]23509
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]23502-6
[/TD]
[TD]12/07/2017
[/TD]
[TD]Contract
[/TD]
[TD]Visit completed
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]23502-7
[/TD]
[TD]29/08/2017
[/TD]
[TD]Contract
[/TD]
[TD]Visit completed
[/TD]
[TD][/TD]
[TD="colspan: 6"]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.
[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]23502-8
[/TD]
[TD]11/10/2017
[/TD]
[TD]Contract
[/TD]
[TD]Visit completed
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]s23502-8(1)
[/TD]
[TD]02/11/2017
[/TD]
[TD]Rodents
[/TD]
[TD]Visit completed
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]23502-9
[/TD]
[TD]21/11/2017
[/TD]
[TD]Contract
[/TD]
[TD="colspan: 2"]Contract Routine
[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]23502-10
[/TD]
[TD]18/01/2018
[/TD]
[TD]Contract
[/TD]
[TD]Visit completed
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]23509-3
[/TD]
[TD]04/04/2017
[/TD]
[TD]Contract
[/TD]
[TD]Visit completed
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]23509-4
[/TD]
[TD]13/04/2017
[/TD]
[TD]Contract
[/TD]
[TD]Visit completed
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD]23509-5
[/TD]
[TD]07/06/2017
[/TD]
[TD]Contract
[/TD]
[TD]Visit completed
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD]23509-6
[/TD]
[TD]12/07/2017
[/TD]
[TD]Contract
[/TD]
[TD]Visit completed
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD]23509-7
[/TD]
[TD]29/08/2017
[/TD]
[TD]Contract
[/TD]
[TD]Visit completed
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
If you have data like this, you can use this:


Excel 2016
ABCD
1Job numbervisit dateJob typeStatus
223502-304/04/2017ContractVisit completed
323502-413/04/2017ContractVisit completed
423502-507/06/2017ContractVisit completed
523502-612/07/2017ContractVisit completed
623502-729/08/2017ContractVisit completed
723502-811/10/2017ContractVisit completed
8s23502-8(1)02/11/2017RodentsVisit completed
923502-921/11/2017ContractContract Routine
1023502-1018/01/2018ContractVisit completed
1123509-304/04/2017ContractVisit completed
1223509-413/04/2017ContractVisit completed
1323509-507/06/2017ContractVisit completed
1423509-612/07/2017ContractVisit completed
1523509-729/08/2017ContractVisit completed
15



Unknown
FGHIJKLMNO
11st2nd3rd4th5th
2123456789
323502 04/04/201713/04/201707/06/201712/07/201729/08/201711/10/2017
42350904/04/201713/04/201707/06/201712/07/201729/08/2017
5
6
7
8
9
10
15
Cell Formulas
RangeFormula
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;"";"";@
 
Last edited:
Upvote 0
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




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

Excel 2016
ABCD
Job numbervisit dateJob typeStatus
23502-3ContractVisit completed
23502-4ContractVisit completed
23502-5ContractVisit completed
23502-6ContractVisit completed
23502-7ContractVisit completed
23502-8ContractVisit completed
s23502-8(1)RodentsVisit completed
23502-9ContractContract Routine
23502-10ContractVisit completed
23509-3ContractVisit completed
23509-4ContractVisit completed
23509-5ContractVisit completed
23509-6ContractVisit completed
23509-7ContractVisit completed

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]04/04/2017[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]13/04/2017[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]07/06/2017[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]12/07/2017[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]29/08/2017[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]11/10/2017[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]02/11/2017[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]21/11/2017[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]18/01/2018[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]04/04/2017[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]13/04/2017[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]07/06/2017[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]12/07/2017[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]29/08/2017[/TD]

</tbody>
15​



Unknown
FGHIJKLMNO

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]1st[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]2nd[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]3rd[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]4th[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]5th[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]23502[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]04/04/2017[/TD]
[TD="align: right"]13/04/2017[/TD]
[TD="align: right"]07/06/2017[/TD]
[TD="align: right"]12/07/2017[/TD]
[TD="align: right"]29/08/2017[/TD]
[TD="align: right"]11/10/2017[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]23509[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]04/04/2017[/TD]
[TD="align: right"]13/04/2017[/TD]
[TD="align: right"]07/06/2017[/TD]
[TD="align: right"]12/07/2017[/TD]
[TD="align: right"]29/08/2017[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
15​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G3[/TH]
[TD="align: left"]=SUMIFS($B$2:$B$15,$A$2:$A$15,$F3&"-"&G$2,$C$2:$C$15,"Contract",$D$2:$D$15,"Visit Completed")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Copy the formula down and across and use custom format with this: dd/mm/yyyy;"";"";@
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
How to make this IF formula work?
Replies
1
Views
252
Fluff
Fluff
N
  • Solved
Matching multiple ways?
Replies
3
Views
406
Noxqss38242
N
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
423
dshtesy
D
A
  • Solved
AVERAGEIFS with 4 criteria
2
Replies
16
Views
761
Fluff
Fluff
M
  • Question Question
select value two columns to the left of variable in an array
Replies
1
Views
279
myall_blues
myall_blues

Forum statistics

Threads
1,224,823
Messages
6,181,182
Members
453,021
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top