sumif formula

M

mary200668

New Member
Joined
Aug 30, 2017
Messages
2
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?
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
=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
 
Upvote 0
03/08/2017mot03/08/2018
05/08/2017insp28/10/2017
07/08/2017mot07/08/2018
09/08/2017insp01/11/2017mytable
11/08/2017mot11/08/2018
13/08/2017insp05/11/2017
15/08/2017mot15/08/201801/08/201701/08/201824/10/2017
17/08/2017insp09/11/201702/08/201702/08/201825/10/2017
19/08/2017mot19/08/201803/08/201703/08/201826/10/2017
21/08/2017insp13/11/201704/08/201704/08/201827/10/2017
23/08/2017mot23/08/201805/08/201705/08/201828/10/2017
25/08/2017insp17/11/201706/08/201706/08/201829/10/2017
27/08/2017mot27/08/201807/08/201707/08/201830/10/2017
29/08/2017insp21/11/201708/08/201708/08/201831/10/2017
31/08/2017mot31/08/201809/08/201709/08/201801/11/2017
30/08/2017insp22/11/201710/08/201710/08/201802/11/2017
28/08/2017mot28/08/201811/08/201711/08/201803/11/2017
12/08/201712/08/201804/11/2017
13/08/201713/08/201805/11/2017
14/08/201714/08/201806/11/2017
15/08/201715/08/201807/11/2017
16/08/201716/08/201808/11/2017
C117/08/201717/08/201809/11/2017
=IF(B1="mot",VLOOKUP(A1,mytable,2),VLOOKUP(A1,mytable,3))18/08/201718/08/201810/11/2017
19/08/201719/08/201811/11/2017
20/08/201720/08/201812/11/2017
21/08/201721/08/201813/11/2017
22/08/201722/08/201814/11/2017
23/08/201723/08/201815/11/2017
24/08/201724/08/201816/11/2017
25/08/201725/08/201817/11/2017
26/08/201726/08/201818/11/2017
27/08/201727/08/201819/11/2017
28/08/201728/08/201820/11/2017
29/08/201729/08/201821/11/2017
30/08/201730/08/201822/11/2017
31/08/201731/08/201823/11/2017

<colgroup><col><col><col><col span="5"><col span="3"><col span="2"></colgroup><tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
An MOT due date formula
Replies
6
Views
551
Joe4
Joe4
J
  • Solved
Excel formula help!!!
Replies
4
Views
302
felixstraube
F
O
  • Question
XLookup HELP!!
Replies
2
Views
328
Alex Blakenburg
A
C
  • Solved
Updating a specific cell value in an autofiltered table
Replies
3
Views
250
skillilea
S
S
  • Question
IF cells in column A contains a dates that are 7 days before todays date then output the date and the value in the coulumn next to it in a list
Replies
1
Views
415
etaf
etaf

Forum statistics

Threads
1,215,580
Messages
6,125,654
Members
449,245
Latest member
PatrickL

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