Find a row based on multiple criteria and then sum up items that are in that row to the right.

[agcoleman]

I am trying to find a formula or combination of formulas so that I can find a row and the sum up some values that are in that same row but to the right.
The picture below gives an example. I need to be able to write a formula that will look at the first 4 columns and match them to a criteria on a different tab. Once that row is found I need to be able to sum the cells that are out to the right.

criteria 1	criteria 2	criteria 3	criteria 4	Cell to sum	Cell to sum	Cell to sum	Cell to sum
A	B	C	D	1​	2​	3​	4​
E	B	C	D	2​	3​	4​	5​

 

[Fluff]

Hi & welcome to MrExcel.
Can you post what your criteria are & how it's laid out.

 

[agcoleman]

Hi & welcome to MrExcel.
Can you post what your criteria are & how it's laid out.

The attached picture should give you a better idea.
I need to be able to look at the criteria in columns B through U and match those to an identical setup on another tab. Once the criteria in columns B through U have been matched then I want to sum up that row in columns V-Z.

 

[Fluff]

With that many columns to match, are you happy to use a helper column?

 

[Sufiyan97]

If I am not miss-interpreting you requirement
is that what you want?

Sheet1

Book3
	A	B	C	D	E	F	G	H
1	Criteria 1	Criteria 2	Criteria 3	Criteria 4	Cell to Sum	Cell to Sum	Cell to Sum	Cell to Sum
2	A	B	C	D	1	2	3	4
3	E	B	C	D	2	3	4	5
Sheet1

Sheet2

Book3
	A	B	C	D	E
1	Criteria 1	Criteria 2	Criteria 3	Criteria 4	Sum
2	A	B	C	D	10
3	E	B	C	D	14
Sheet2

Cell Formulas
Range	Formula
E2:E3	E2	=IF(CONCATENATE(Sheet1!A2,Sheet1!B2,Sheet1!C2,Sheet1!D2)=CONCATENATE(A2,B2,C2,D2),SUM(Sheet1!E2:H2),0)

 

[Fluff]

That assumes the matches will be on the same row on both sheets, which from what the OP has said, I doubt.

 

[Sufiyan97]

You are right Fluff, I just realized after posting.

 

[agcoleman]

That assumes the matches will be on the same row on both sheets, which from what the OP has said, I doubt.

Correct, the criteria will be on different rows in the second sheet so it can't reference a specific row. It will be on various rows on sheet two.

 

[Fluff]

And the answer to my question in post#4?

 

[agcoleman]

With that many columns to match, are you happy to use a helper column?

I am open to it if that is the only way.