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?

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?

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?

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?

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?

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?

..... place in I15.

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

The I15 gives me no output whereas the correct output would be 40000.

I dont understand why you are offsetting anything in Sheet 2. The data to be
summed is in Sheet 1. The values ABC and 123 would be present only once in
Sheet 2. It is not a range.



"Toppers" wrote:

.... place in I15.

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

=SUMIF(Sheet1!$H:$H,CONCATENATE($A$15,$I$2),Sheet1 !$D:$D)

In your original post you used the expression row X which (at least to me)
implied that the row was variable. If you stated clearly your requirements,
we would have reached an answer much quicker. "I" row 1 was changed to "I"
row 2 [ from your original note ... Sheet 2 Column A Row X & Column I Row 1]

And the formula DOES sum data in sheet1

"zeyneddine" wrote:

The I15 gives me no output whereas the correct output would be 40000.

I dont understand why you are offsetting anything in Sheet 2. The data to be
summed is in Sheet 1. The values ABC and 123 would be present only once in
Sheet 2. It is not a range.



"Toppers" wrote:

.... place in I15.

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