sumif formula

[mary200668]

If cell A has a date, cell B has either MOT or Inspection. How do I create a formula for cell C to calculate one year on if cell b says MOT or 12 weeks on if cell B says Inspection?

 

[oldbrewer]

=if(b1="MOT",today()+365,today()+84)

but note tomorrow all dates will move forward one day

so maybe a lookup table would work better

1/8/17.............1/8/18.............1/11/17

=if(b1="MOT",vlookup(a1,mytable,2),vlookup(a1,mytable,3)

obviously I show only one row of the lookup table

 

[petertenthije]

You would not need a SUMIF formula, but a regular IF formula:

=IF(A1>0,A1+if(B1="MOT",84,365),"")

 

[mary200668]

Are you able to show how i would have it with "mot",365 and inspection,84?

 

[oldbrewer]

[TABLE="width: 877"]
<colgroup><col><col><col><col span="5"><col span="3"><col span="2"></colgroup><tbody>[TR]
[TD="align: right"]03/08/2017[/TD]
[TD]mot[/TD]
[TD="align: right"]03/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05/08/2017[/TD]
[TD]insp[/TD]
[TD="align: right"]28/10/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]07/08/2017[/TD]
[TD]mot[/TD]
[TD="align: right"]07/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09/08/2017[/TD]
[TD]insp[/TD]
[TD="align: right"]01/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]mytable[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11/08/2017[/TD]
[TD]mot[/TD]
[TD="align: right"]11/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13/08/2017[/TD]
[TD]insp[/TD]
[TD="align: right"]05/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15/08/2017[/TD]
[TD]mot[/TD]
[TD="align: right"]15/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]01/08/2018[/TD]
[TD="align: right"]24/10/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17/08/2017[/TD]
[TD]insp[/TD]
[TD="align: right"]09/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02/08/2017[/TD]
[TD="align: right"]02/08/2018[/TD]
[TD="align: right"]25/10/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19/08/2017[/TD]
[TD]mot[/TD]
[TD="align: right"]19/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]03/08/2017[/TD]
[TD="align: right"]03/08/2018[/TD]
[TD="align: right"]26/10/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21/08/2017[/TD]
[TD]insp[/TD]
[TD="align: right"]13/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]04/08/2017[/TD]
[TD="align: right"]04/08/2018[/TD]
[TD="align: right"]27/10/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23/08/2017[/TD]
[TD]mot[/TD]
[TD="align: right"]23/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]05/08/2017[/TD]
[TD="align: right"]05/08/2018[/TD]
[TD="align: right"]28/10/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25/08/2017[/TD]
[TD]insp[/TD]
[TD="align: right"]17/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]06/08/2017[/TD]
[TD="align: right"]06/08/2018[/TD]
[TD="align: right"]29/10/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]27/08/2017[/TD]
[TD]mot[/TD]
[TD="align: right"]27/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]07/08/2017[/TD]
[TD="align: right"]07/08/2018[/TD]
[TD="align: right"]30/10/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29/08/2017[/TD]
[TD]insp[/TD]
[TD="align: right"]21/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]08/08/2017[/TD]
[TD="align: right"]08/08/2018[/TD]
[TD="align: right"]31/10/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/08/2017[/TD]
[TD]mot[/TD]
[TD="align: right"]31/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]09/08/2017[/TD]
[TD="align: right"]09/08/2018[/TD]
[TD="align: right"]01/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/08/2017[/TD]
[TD]insp[/TD]
[TD="align: right"]22/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/08/2017[/TD]
[TD="align: right"]10/08/2018[/TD]
[TD="align: right"]02/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28/08/2017[/TD]
[TD]mot[/TD]
[TD="align: right"]28/08/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11/08/2017[/TD]
[TD="align: right"]11/08/2018[/TD]
[TD="align: right"]03/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12/08/2017[/TD]
[TD="align: right"]12/08/2018[/TD]
[TD="align: right"]04/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13/08/2017[/TD]
[TD="align: right"]13/08/2018[/TD]
[TD="align: right"]05/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14/08/2017[/TD]
[TD="align: right"]14/08/2018[/TD]
[TD="align: right"]06/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15/08/2017[/TD]
[TD="align: right"]15/08/2018[/TD]
[TD="align: right"]07/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16/08/2017[/TD]
[TD="align: right"]16/08/2018[/TD]
[TD="align: right"]08/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]C1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17/08/2017[/TD]
[TD="align: right"]17/08/2018[/TD]
[TD="align: right"]09/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]=IF(B1="mot",VLOOKUP(A1,mytable,2),VLOOKUP(A1,mytable,3))[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18/08/2017[/TD]
[TD="align: right"]18/08/2018[/TD]
[TD="align: right"]10/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19/08/2017[/TD]
[TD="align: right"]19/08/2018[/TD]
[TD="align: right"]11/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20/08/2017[/TD]
[TD="align: right"]20/08/2018[/TD]
[TD="align: right"]12/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21/08/2017[/TD]
[TD="align: right"]21/08/2018[/TD]
[TD="align: right"]13/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]22/08/2017[/TD]
[TD="align: right"]22/08/2018[/TD]
[TD="align: right"]14/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23/08/2017[/TD]
[TD="align: right"]23/08/2018[/TD]
[TD="align: right"]15/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]24/08/2017[/TD]
[TD="align: right"]24/08/2018[/TD]
[TD="align: right"]16/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]25/08/2017[/TD]
[TD="align: right"]25/08/2018[/TD]
[TD="align: right"]17/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]26/08/2017[/TD]
[TD="align: right"]26/08/2018[/TD]
[TD="align: right"]18/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]27/08/2017[/TD]
[TD="align: right"]27/08/2018[/TD]
[TD="align: right"]19/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]28/08/2017[/TD]
[TD="align: right"]28/08/2018[/TD]
[TD="align: right"]20/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29/08/2017[/TD]
[TD="align: right"]29/08/2018[/TD]
[TD="align: right"]21/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30/08/2017[/TD]
[TD="align: right"]30/08/2018[/TD]
[TD="align: right"]22/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31/08/2017[/TD]
[TD="align: right"]31/08/2018[/TD]
[TD="align: right"]23/11/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]