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]

Ah my bad, forgot to add that in. In that case, change the SUMIF to a SUMIFS statement.

=IF(B3="REVEX", SUMIFS($C$8:$C$23,$A$8:$A$23,A3,$B$8:$B$23, "Actual LTD (Life to Date)"), IF(B3="CAPEX", SUMIFS($D$8:$D$23,$A$8:$A$23,A3,$B$8:$B$23, "Actual LTD (Life to Date)"), "There was an error"))

To make the range dynamic, just remove the row references so that the ranges in the SUMIFS are simply the column letter. Like SUMIFS(C:C, A:A, A3, B:B, "Actual LTD (Life to Date)")

View attachment 43580

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

Final formula - worked perfectly - much gratitude for your expert help.

Have a great day/evening

 

[Alex Blakenburg]

Just a suggestion, if the row coloring is indicative of you using an Excel Table, consider modifying richh' formula to use "table column" references so that you are not referencing over a million rows by using the A:A type of referencing.
The Table Column reference will autoexpand as the table expands.

 

[Peter_SSs]

This is a new type of formula on me but have replaced with table name per instructions but am getting formula error =SUM(FILTER(FILTER(Additional test data2,Additional test data2[#Headers]=B2),(Additional test data2[Job Code]=A2)*(Additional test data2[Cost Type]="Actual LTD (Life to Date)")))

Additional test data2 is not a valid table name as spaces are not allowed in table names.
You should be able to check the table name via the Formulas ribbon tab then Name Manager

 

[Jon3sy]

Just a suggestion, if the row coloring is indicative of you using an Excel Table, consider modifying richh' formula to use "table column" references so that you are not referencing over a million rows by using the A:A type of referencing.
The Table Column reference will autoexpand as the table expands.

Thank you Alex for suggestion - the row colouring was formatting on the original sheet where I was endeavouring to highlight the subtotal rows for the sheet. I'm not sure of the logic on how to use the table column references in my final formula but will give it a try.

 

[Alex Blakenburg]

To convert a range to a table.

• Click anywhere in the table and hit ctrl+T
  (if you need to make sure the "my table has header" box is ticked)
• Give the table a meaningful name by
  • clicking on the Table Design Tab (far right)
  • then put a name in the Table Name box (far left)
• To select the whole table column in a formula, have a look at this from Jon Acampora Excel Campus
  1. Selecting Rows and Columns

 

[richh]

Good point, Alex. In reality, this type of table and the results attempting to be generated could be accomplished quite quickly via a pivot table, which would curcumvent the need to do any hard formulas.

 

[Jon3sy]

Good point, Alex. In reality, this type of table and the results attempting to be generated could be accomplished quite quickly via a pivot table, which would curcumvent the need to do any hard formulas.

But how would a pivot work with the 2 different data sources? Neither of which are static - they are both refreshed manually to each tab in this workbook (at this stage via paste special values only) from reports generated out of another system - the only fields that remain untouched in the sheet are the formula columns.

 

[richh]

Power Query. Rather than manually refresh the data, just build a query that runs and pulls the data for you into a table (or tables). then, pivots can be created that just update once the data gets yoinked in.

 

[Jon3sy]

Oh ok, of course. Have been having a play around with named table ranges and formatting, etc so thanks for the help with all this - plenty of options now - is only meant to be an interim solution. Best regards

 

[Peter_SSs]

Have been having a play around with named table ranges

So if you now have a formal table for the source data, have you re-tried the post #5 suggestion?