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),Sheet1!$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 said:
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 said:
=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
:
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.
:
.... place in I15.
:
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.
:
If I understand correctly, try:
=SUMPRODUCT(--(Sheet1!$A$1:$A$20=Sheet2!I1&Sheet2!$A$1:$A$20)*(Sheet1!$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
:
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',Sheet1!$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?
