sumif from different column [SOLVED]

[hanim554]

Dear Friends,


I have in sheet2 unique name in the column A in couumn C to Z different value.


in Sheet Report in column C i have same unique name and i want sum of C to Z from Sheet2, i can use 'sumif' if value from one column, i have to use sumif + sumif from C to Z.

Is there any way to do this, please help me.


Regards

Hanim

 

[vijay.vizzu]

Dear


In SUMIF(range, criteria,[sum_range]), in sum_range you can select entire range from C to Z, it will sum all the values for the criteria you specified


Regards

Vijay

 

[Colin Legg]

Hi Hanim,


To do this with

SUMIF() you can use a helper column in Sheet2. In Sheet2, in a spare column, put a [code]SUM formula like this (assuming data starts from row 2):

[code]=SUM(C2:Z2)

and fill down the spare column. Let's say that this spare column is column AA. Then you can reference column AA from your [code]SUMIF() formula, eg:


[code]=SUMIF(Sheet2!A2:A100,C2,Sheet2!AA2:AA100)

Another way of doing this would be to use SUMPRODUCT()[/code]. If you use SUMPRODUCT()[/code] then you won't need the helper column in Sheet2, but the formula is more complicated and slower to calculate, so personally I prefer the SUMIF()[/code] method.


=SUMPRODUCT((Sheet2!A2:A100=C2)*Sheet2!C2:Z2)[/code]

 

[Colin Legg]

Hi Vijay,


The dimensions of

Range will cause [code]sum_range

to be resized internally within the SUMIF()[/code] calculation, so that won't work. . It's quite a nasty one because it looks like it should work. For more information please see section 3.2:

http://colinlegg.wordpress.com/2012/04/06/sumif-formulas/


There's also an example of how to workaround it using the helper column suggestion about in section 5 'How Can I Sum A Range Which Has Different Dimensions To The Criteria Range?'

 

[vijay.vizzu]

Thank so much colin for your valuable comment/suggestion

 

[Haseeb A]

Hello Hanim,


I would suggest to use a helper column as Colin said to speed up the spreadsheet.


Here is an alternate with SUMIF & OFFSET. But remember OFFSET is volatile function, so this may slow down sheet performance.


=SUMPRODUCT(SUMIF(Sheet2!A$2:A$1000,C2,OFFSET(Sheet2!C$2:C$1000,,COLUMN(C$2:Z$2)-COLUMN(C$2))))


Here is Hui's formula forensic,


http://chandoo.org/wp/2012/09/20/formula-forensics-no-029/


Haseeb

 

[Sajan]

Hi Hanim,

Here is one more option:

=SUM((Sheet2!$A$2:$A$1000=C2)* MMULT(--Sheet2!$C$2:$Z$1000,TRANSPOSE(COLUMN(Sheet2!$C$2:$Z$1000)^0)))


enter with Ctrl+Shift+Enter (instead of Enter key)


It will support numbers or blanks in the data range (C2:Z1000), but if you have other characters, additional steps will need to be taken to prevent errors.


As an alternative, you could use the MMULT portion array-entered over rows 2:1000 (say in column B of Sheet2) to be used as a helper column.


Cheers,

Sajan.

 

[jeffreyweir]

My thoughts:

1. Any reason why you can't use a Pivot Table? PivotTables were meant for this kind of thing.

2. If you want a formula, then I would go with the SUMPRODUCT formula Colin mentions above, because it's simplist for someone else to understand. i.e. =SUMPRODUCT((Sheet2!A2:A100=C2)*Sheet2!C2:Z2)


Personally I would use a Pivot Table. Additionally, if you make the data in sheet2 into an Excel Table using the Ctrl T keyboard shortcut (available in Excel 2007 or later) then the table will autoexpand, and so the Pivot will always reference the entire dataset. Plus you can have the pivot automatically refreshed any time the data sheet is deactivated or the summary sheet is activated.

 

[hanim554]

Thank you so much all friends.


sorry for the late reply.


Regards


Hanim