Try:

=SUMIF(Sheet1!$H$1:$H$20,CONCATENATE(OFFSET($A$1,R OW()-1,,,1),$I$2),(Sheet1!$D$1:$D$20))

HTH

"zeyneddine" wrote:

Using my example, the answer will go into:
(Sheet 2 Column I Row 15)

"Toppers" wrote:

Which row does the result go in (using your example, is it row 15)?

"zeyneddine" wrote:

Can you please explain your function?

Let me clarify my question with examples
Sheet 1 Column H Row 5 = ABC123; Sheet 1 Column D Row 5 = 1234
Sheet 1 Column H Row 6 = ABC123; Sheet 1 Column D Row 6 = 5678

Sheet 2 Column A Row 15 = ABC
Sheet 2 Column I Row 2 = 123

In simple words, IF (Sheet 2 Column A Row 15 & Sheet 2 Column I Row 2) is
equal to (Sheet 1 Column H Row 5) and (Sheet 1 Column H Row 6), sum up (Sheet
1 Column D Row 5) and (Sheet 1 Column D Row 6), that is 1234+5678.

If (Sheet 2 Column A Row 15 & Sheet 2 Column I Row 2) is not matched with
anything on Sheet 1, return nothing.

Please help.


"Toppers" wrote:

If I understand correctly, try:

=SUMPRODUCT(--(Sheet1!$A$1:$A$20=Sheet2!I1&Sheet2!$A$1:$A$20)*(S heet1!$D$1:$D$20))

All ranges must be same size i.e. same numbr of rows AND cannot be a column
i.e A;A is not allowed.

HTH

"zeyneddine" wrote:

Hi. I have a sheet 1 with columns as:
A B C D E F G H I
I have sheet 2 with columns as
A B C D E F G H I

The question is if (Sheet 1 Column H) is the same as (Sheet 2 Column A Row X
& Column I Row 1), obtain value in (Sheet 1 Column D, same row) and enter it
in a field in Sheet 2. The current setup is
=IF(ISERROR(OFFSET(Sheet1!$H$1,MATCH(I$1&$A'X',She et1!$H:$H,0)-1,-4)),0,OFFSET(Sheet1!$H$1,MATCH(I$1&$A'X',Sheet1$H: $H,0)-1,-4))

However, (Sheet 1 Column H) can have more than one value same as (Sheet 2
Column A & Column I Row 1), and I want to sum up the values and enter the sum
into a field in Sheet 2. Can anyone assist?