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?

Why are you yo using row 20? Could you verbally explain the following function?

"Toppers" wrote:

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?

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?

In the example I look at the range H1 to H20 but you need to adjust this to
suit ; you can set to the total columns as below:

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

The SUMIF function compares the value in "A" row [e.g A15 if row=15] & I2
against H1 to H20 and if it finds a match it sums the corresponding value(s)
in D1 to D20.

If your data in Sheet2 starts in column 3 then enter this formula in column
3 and copy down until you reach the last entry in column A.

I assume I2 is "fixed" ... in your first post you said I1! .. hence the
absolute address $I$2.

I wil sign-off now (late UK time) so I hope you can get this working.

"zeyneddine" wrote:

Why are you yo using row 20? Could you verbally explain the following function?

"Toppers" wrote:

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?

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

Thank you Toppers. This works to sum up the two values in Sheet 1. However,
can you please expand the formula to include an error check, such that if
Sheet 1 does not contain any value in (Column H Row 5) that matches the
concatenated output in Sheet 2, the return would be nothing?

"Toppers" wrote:

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

By default, it will return 0 (zero) if no match is found. If you want it to
return blank (nothing?) then :

=IF(SUMIF(Sheet1!$H:$H,CONCATENATE($A$15,$I$2),She et1!$D:$D)=0,"",SUMIF(Sheet1!$H:$H,CONCATENATE($A$ 15,$I$2),Sheet1!$D:$D))

OR

=IF(SUMIF(Sheet1!$H:$H,$A$15&$I$2,Sheet1!$D:$D)=0, "",SUMIF(Sheet1!$H:$H,$A$15&$I$2,Sheet1!$D:$D) )

An alternative is:

=IF(SUMPRODUCT(--(Sheet1!$H1:$H20=$A$15&$I$2),(Sheet1!$D1:$D20))=0, "",SUMPRODUCT(--(Sheet1!$H1:$H20=$A$15&$I$2),(Sheet1!$D1:$D20)))


"zeyneddine" wrote:

Thank you Toppers. This works to sum up the two values in Sheet 1. However,
can you please expand the formula to include an error check, such that if
Sheet 1 does not contain any value in (Column H Row 5) that matches the
concatenated output in Sheet 2, the return would be nothing?

"Toppers" wrote:

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

Thank you very much Toppers!

"Toppers" wrote:

By default, it will return 0 (zero) if no match is found. If you want it to
return blank (nothing?) then :

=IF(SUMIF(Sheet1!$H:$H,CONCATENATE($A$15,$I$2),She et1!$D:$D)=0,"",SUMIF(Sheet1!$H:$H,CONCATENATE($A$ 15,$I$2),Sheet1!$D:$D))

OR

=IF(SUMIF(Sheet1!$H:$H,$A$15&$I$2,Sheet1!$D:$D)=0, "",SUMIF(Sheet1!$H:$H,$A$15&$I$2,Sheet1!$D:$D) )

An alternative is:

=IF(SUMPRODUCT(--(Sheet1!$H1:$H20=$A$15&$I$2),(Sheet1!$D1:$D20))=0, "",SUMPRODUCT(--(Sheet1!$H1:$H20=$A$15&$I$2),(Sheet1!$D1:$D20)))


"zeyneddine" wrote:

Thank you Toppers. This works to sum up the two values in Sheet 1. However,
can you please expand the formula to include an error check, such that if
Sheet 1 does not contain any value in (Column H Row 5) that matches the
concatenated output in Sheet 2, the return would be nothing?

"Toppers" wrote:

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