Spreadsheet: 84672d1284693703-combining-look-up-if-and-sumifs-sample-formula
Sheet: File

 |  | [NAME] |  | [NAME] needed |  | [NAME] needed
Code | Shortfall | Total Quantity to Produce | Notes | Dates of Production | Notes | Commitment | Notes
1000 | 0 | 0 | If Column B <= 0, 0 to return |  | If Column B <= 0, no data to return |  | If Column B <= 0, no data to return
1001 | -26936 | 0 | If Column B <= 0, 0 to return |  | If Column B <= 0, no data to return |  | If Column B <= 0, no data to return
1002 | -16090 | 0 | If Column B <= 0, 0 to return |  | If Column B <= 0, no data to return |  | If Column B <= 0, no data to return
1003 | -19819 | 0 | If Column B <= 0, 0 to return |  | If Column B <= 0, no data to return |  | If Column B <= 0, no data to return
1004 | 9640 | 10600 | If Column B >= 1, sum data on Data tab column B with the same code(vlookup(File!A7, Data!A2:A159,1,false)), with additional condition that IF Data!E:E is "N". | 1500 - [PHONE] - [PHONE] - [PHONE] - [PHONE] - [PHONE] - 20110114 | How will I return the summed data in Data!C:C like this? | abcdef | How will I return the data in Data! F:F(same code) like this? It is in the row where we get the 1200 - 2010114 (last value that we summed). | 13000
1005 | 556 | 0 | Since Column Data!E:E is = "Y", 0 will be returned |  | Since Column Data!E:E is = "Y" no data will be returned |  | Since Column Data!E:E is = "Y" no data will be returned
1006 | 182 | 0 | Since there's no data in this Code, 0 will be returned |  | Since there's no data in this Code, 0 will be returned |  | Since there's no data in this Code, 0 will be returned
1007 | -21379
1010 | -4559
1012 | 0
1015 | -10576
1016 | 12560
1033 | -145824
1037 | -5625
1061 | 1008
1062 | 1650
1063 | 2304
1065 | 0
1073 | 0
1074 | -2675
1075 | 0
1076 | 0
1079 | 0
1085 | 0
1087 | 4500
1088 | 8550
1089 | 9450
1090 | 922
1149 | 637
1180 | 800
30037 | -17776
53110 | 0
53111 | -1345
60014 | 672
60044 | -610
60049 | 1440
60051 | 0
60111 | 0
61024 | 5750
61025 | 4650
61026 | 7990
61027 | 3610
61048 | 678
61101 | 2081
61102 | 1760
61103 | 3108
71084 | -2615
71109 | 1418
71110 | 1646
71111 | 495
71112 | 1030
71113 | 1250
71114 | 1160
71245 | -2234
71245 | 0
71301 | -650
71302 | 1058
71303 | 306
71304 | 260
71305 | 105
71306 | -1837
71307 | -3667
71309 | 71
71311 | 62
71356 | -17187
71357 | -19526
71358 | -11934
71359 | 2718
71360 | -2675
71367 | -31
71370 | -3293
71372 | 1473
71374 | 214
71375 | 1922
71376 | -2927
71377 | 0
71379 | -6916
71381 | 186
71382 | -1978
71383 | -3096
71385 | 0
81033 | -6094
81034 | 0
81035 | 560
81036 | 4201
81037 | -175
81046 | -136
81059 | 8083
81121 | -355
81122 | -4884
81122 | 964
81123 | 728
81124 | -2314
81135 | 240
81157 | -41635
81158 | -14045
81310 | 1118
81311 | -2047
91004 | -47621
91006 | 5760
91007 | 5520
91010 | 6240
91012 | -4477
91013 | 5440
91014 | 0
91017 | 2880
91018 | -5215
91019 | 1404
91024 | 3520
91025 | 1280
91026 | -122773
91027 | 320
91028 | 7200
91029 | 11200
91031 | 0
91037 | 241
91038 | 3130
91039 | 1129
91040 | -3892
91041 | -5223
91042 | -8325
91043 | 301
91044 | 5913
91045 | -2842
91060 | 7097
91061 | -5503
91062 | 11440
91063 | -6396
91065 | 2048
91070 | -79182
91071 | -428
91077 | -19061
91078 | -23582
91079 | -57179
91080 | 136
91081 | 760
91082 | 903
91087 | -5117
91088 | -10931
91090 | -53924
91091 | 1024
91092 | -23901
91092 | 2369
91093 | -45730
91100 | 1757
91101 | -777
91102 | -2187
91103 | 133
91440 | 229