Sumproduct formula for multiple criteria vlookup to source tab

[Jon3sy]

I have two tabs of data = main tab where I'm writing the formula+ additional source data that has matching specific criteria (record #, field label, Funding type - revex or capex), I am wanting my formula to return value from Column c of source file if it meets the first 3 arguments (Code matches source tab in column A and Actual LTD and Revex - use col C value, if 3rd criteria =Capex use col D value). Not having much luck - been a while since have had to write formulas. Appreciate anyone's help.

=SUMPRODUCT(('Test 2 MonitorData'!$A2='Additional test data2'!$A$2:$A$25000)*('Additional test data2'!$B$2:$B$25000="Actual LTD (Life to Date)")*(('Test 2 MonitorData'!$E2="Revex")*('Additional test data2'!$C2))+('Test 2 MonitorData'!$E2="Capex")*('Additional test data2'!$D2))

In the main tab the lookup value for funding type is in a single column whereas in the source file below it is split out.

Job Code	Cost type	REVEX	CAPEX
KI000133	Total EAC (All Years)	31,444.10	0
KI000133	Actual LTD (Life to Date)	31,444.10	0
KI000133	ETC YE	0	0
KI000133	Actual CY	10,295	0
KI000133	EAC CY	10,295	0

 

[Jon3sy]

So I'm heading back to this formula because I now realise I need to add another argument into it and not sure where/how to do that without spending too much time on it, so was thinking the formula would need to first look at M2 and if it ="Variation Estimate", return 0 else do the sumif as per the rest of the formula - would that be the right way to go and would it mean adding a vlookup into the formula syntax?

=IF(E2="REVEX", SUMIFS('Additional test data2'!C:C,'Additional test data2'!A:A,A2,'Additional test data2'!B:B,"Actual LTD (Life to Date)"), IF(E2="CAPEX", SUMIFS('Additional test data2'!D:D,'Additional test data2'!A:A,A2,'Additional test data2'!B:B,"Actual LTD (Life to Date)"),0))

 

[Alex Blakenburg]

Does this do what you want ?
eg exclude anything for both REVEX & CAPEX that has Variation Estimate in column M

=IF(E2="REVEX",
  SUMIFS('Additional test data2'!C:C,
      'Additional test data2'!A:A,A2,
      'Additional test data2'!B:B,"Actual LTD (Life to Date)"
      'Additional test data2'!M:M,"<>" & "Variation Estimate"),
IF(E2="CAPEX",
  SUMIFS('Additional test data2'!D:D,
      'Additional test data2'!A:A,A2,
      'Additional test data2'!B:B,"Actual LTD (Life to Date)"
      'Additional test data2'!M:M,"<>" & "Variation Estimate"),
0))

 

[Jon3sy]

Does this do what you want ?
eg exclude anything for both REVEX & CAPEX that has Variation Estimate in column M

=IF(E2="REVEX",
  SUMIFS('Additional test data2'!C:C,
      'Additional test data2'!A:A,A2,
      'Additional test data2'!B:B,"Actual LTD (Life to Date)"
      'Additional test data2'!M:M,"<>" & "Variation Estimate"),
IF(E2="CAPEX",
  SUMIFS('Additional test data2'!D:D,
      'Additional test data2'!A:A,A2,
      'Additional test data2'!B:B,"Actual LTD (Life to Date)"
      'Additional test data2'!M:M,"<>" & "Variation Estimate"),
0))

Hi Alex - yes I'm not sure I gave the correct wording but I want the formula to place a zero in the cell if M2 = variation estimate, else perform the rest of the calculation per the formula. Your formula didn't work - so I think it first needs to check column M and perform that part of formula, then if M does not = variation estimate it performs rest of calculation - hope that makes sense? Much appreciation

 

[Alex Blakenburg]

See if this is what you are after:-

=IF(M2="Variation Estimate",0,
IF(E2="REVEX",
  SUMIFS('Additional test data2'!C:C,
      'Additional test data2'!A:A,A2,
      'Additional test data2'!B:B,"Actual LTD (Life to Date)"),
IF(E2="CAPEX",
  SUMIFS('Additional test data2'!D:D,
     'Additional test data2'!A:A,A2,
     'Additional test data2'!B:B,"Actual LTD (Life to Date)"),
0)))

 

[Jon3sy]

See if this is what you are after:-

=IF(M2="Variation Estimate",0,
IF(E2="REVEX",
  SUMIFS('Additional test data2'!C:C,
      'Additional test data2'!A:A,A2,
      'Additional test data2'!B:B,"Actual LTD (Life to Date)"),
IF(E2="CAPEX",
  SUMIFS('Additional test data2'!D:D,
     'Additional test data2'!A:A,A2,
     'Additional test data2'!B:B,"Actual LTD (Life to Date)"),
0)))

Thank you Alex that worked perfectly - that is the formula I had with exception to a bracket after the 0 and missing a bracket at the end and therefore it wouldn't work. Very much appreciated. Thank you for our time. Happy Days

 

[Alex Blakenburg]

Glad we were able to help. Thanks for the feedback.