Sum up columns in different sheet with error check
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?
|