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.
=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 |