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

 

[richh]

Hi Jonesy,

This is a wee bit confusing... You say you have three arguments to check against, yet you only list two. Also, the headers you list for the source data don't match up with the code you've written nor the example written:

matching specific criteria (record #, field label, Funding type - revex or capex)

Seems like those are the headers for Source Data but they don't match the table shown nor the columns in your formula (You're looking at Column E but you only have three headers in your description). Very hard to follow. Perhaps if you showed an example of the source data, it might make more sense to me (Sorry, not trying to be rude, just prefacing my solution if it doesn't match what you're looking for).

That being said, I've attempted to solve your problem with what I hope is what you're looking for using SUMIFS rather than SUMPRODUCT.

In my solution, the Source Data Column Headers match the table shown in your example.

For the REVEX Column:
=SUMIFS('Source Data'!C:C,'Source Data'!$A:$A,'Main Sheet'!$A2,'Source Data'!$B:$B, 'Main Sheet'!$B2)

For the CAPEX Column:
=SUMIFS('Source Data'!D:D,'Source Data'!$A:$A,'Main Sheet'!$A2,'Source Data'!$B:$B, 'Main Sheet'!$B2)

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Jon3sy]

Apologies - in my haste I did not explain very well and I meant 3 criteria/2 arguments (?) it's been a while since I've used complex formula - I have updated my profile regarding the version I am using, but due to our work protocols cannot seem to install addin, so below I will provide the columns from main sheet where the formula needs to go and the column/row data source example is as per above (each single record will have 5 rows of source data).

The main sheet tab has a single line for each job record per each funding type (so some records will have multiple lines - I use KI000359 as my test example as it has two lines) and the formula to sum the Actual spend to date for each record is looking at the rows in the range of data in sheet 2 where column B cost type = "Actual LTD (Life to Date)" - if in main source the funding type in Column B = "Revex" then take the data from column C, if it = "Capex" then take the data from column D. I'm thinking this might need to be an indirect/match formula?

Thanks again in advance.
S

 

[Peter_SSs]

due to our work protocols cannot seem to install addin,

In that case you can also simply copy/paste a small section of your worksheet into your post. At least that way helpers can still copy that data rather than having to type it all out.

It looks like the data in the source sheet is a formal table so see if this is the sort of thing you are after. Check/edit the table name to match yours.

Jon3sy.xlsm
	A	B	C	D	E
1	Job Code	Cost Type	REVEX	CAPEX	Total
2	JC1	Actual LTD (Life to Date)	159	520	679
3	JC2	ETC YE	163	602	765
4	JC3	ETC YE	363	711	1074
5	JC1	ETC YE	869	821	1690
6	JC2	Actual LTD (Life to Date)	811	689	1500
7	JC3	Actual CY	705	840	1545
8	JC1	Actual LTD (Life to Date)	812	972	1784
9	JC2	Actual CY	852	982	1834
10	JC3	Actual LTD (Life to Date)	265	788	1053
11	JC1	ETC YE	846	426	1272
12	JC2	Actual CY	390	560	950
13	JC3	Actual LTD (Life to Date)	193	910	1103
Source

Jon3sy.xlsm
	A	B	C
1	Job Code	Funding	Total
2	JC1	Revex	971
3	JC1	Capex	1492
Main

Cell Formulas
Range	Formula
C2:C3	C2	=SUM(FILTER(FILTER(Table1,Table1[#Headers]=B2),(Table1[Job Code]=A2)*(Table1[Cost Type]="Actual LTD (Life to Date)")))

 

[Jon3sy]

=SUM(FILTER(FILTER(Table1,Table1[#Headers]=B2),(Table1[Job Code]=A2)*(Table1[Cost Type]="Actual LTD (Life to Date)")))

In that case you can also simply copy/paste a small section of your worksheet into your post. At least that way helpers can still copy that data rather than having to type it all out.

It looks like the data in the source sheet is a formal table so see if this is the sort of thing you are after. Check/edit the table name to match yours.

Jon3sy.xlsm
	A	B	C	D	E
1	Job Code	Cost Type	REVEX	CAPEX	Total
2	JC1	Actual LTD (Life to Date)	159	520	679
3	JC2	ETC YE	163	602	765
4	JC3	ETC YE	363	711	1074
5	JC1	ETC YE	869	821	1690
6	JC2	Actual LTD (Life to Date)	811	689	1500
7	JC3	Actual CY	705	840	1545
8	JC1	Actual LTD (Life to Date)	812	972	1784
9	JC2	Actual CY	852	982	1834
10	JC3	Actual LTD (Life to Date)	265	788	1053
11	JC1	ETC YE	846	426	1272
12	JC2	Actual CY	390	560	950
13	JC3	Actual LTD (Life to Date)	193	910	1103
Source

Jon3sy.xlsm
	A	B	C
1	Job Code	Funding	Total
2	JC1	Revex	971
3	JC1	Capex	1492
Main

Cell Formulas
Range	Formula
C2:C3	C2	=SUM(FILTER(FILTER(Table1,Table1[#Headers]=B2),(Table1[Job Code]=A2)*(Table1[Cost Type]="Actual LTD (Life to Date)")))

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

 

[richh]

Hey again,

Judging by your example, I think you can accomplish your goal with a nest IF/SUMIF statement.

=IF(B2="REVEX", SUMIF(A8:A23,A2,C8:C23), IF(B2="CAPEX", SUMIF(A8:A23,A2,D8:D23), "There was an error"))

 

[Jon3sy]

Hey again,

Judging by your example, I think you can accomplish your goal with a nest IF/SUMIF statement.

=IF(B2="REVEX", SUMIF(A8:A23,A2,C8:C23), IF(B2="CAPEX", SUMIF(A8:A23,A2,D8:D23), "There was an error"))

View attachment 43579

Thank you, I thought you were on to something for a moment but your formula is not referencing the row label in the data source "Actual LTD (Life to Date)" so is adding all rows of data?

 

[Jon3sy]

Thank you, I thought you were on to something for a moment but your formula is not referencing the row label in the data source "Actual LTD (Life to Date)" so is adding all rows of data?

Thank you, I thought you were on to something for a moment but your formula is not referencing the row label in the data source "Actual LTD (Life to Date)" so is adding all rows of data?

Being able to expand the data range automatically in the formula as both the source files grow would be helpful too so the formula doesn't have to be manually changed or monitored?

 

[richh]

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