 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / August 2007Changing the Sum Range
I have an "array formula" that uses multiple criteria to do mimic a "SUMIF" with more than one possible criteria. Essentially it is {SUM(IF((C3:C15<>"X")*(C3:C15<>"Y"),1,0)*(D3:D15))} I want to change the sum range (that is, the (D3:D15)) dependent on some variable. That is, sometimes I want it to use E3:E15 or K3:K15. Is there an easy way to do this? Thanks! Try something like this SUMPRODUCT( (C3:C15<>"X")* (C3:C15<>"Y"), OFFSET(C3;;D1;13)) where 13 is the number of cells you want to sum where D1 is the number of the column (after C) you want to sum. eg.: if D1 = 1, will sum D3:D15 if D1 = 8, will sum K3:K15 If you want everything in variables, try this: SUMPRODUCT( (OFFSET(C3;;C1;13)<>"X")* (OFFSET(C3;;C1;13)<>"Y"), OFFSET(C3;;D1;13)) where C1 is the number of the column (after C) you want to compare. eg.: if C1 = 0, will check C3:C15 if C1 = 2, will check E3:E15  SignatureRodrigo Ferreira Regards from Brazil >I have an "array formula" that uses multiple criteria to do mimic a "SUMIF" > with more than one possible criteria. [quoted text clipped - 7 lines] > > Thanks! That works great! Muito obrigado. > Try something like this > [quoted text clipped - 27 lines] > > > > Thanks! >I have an "array formula" that uses multiple criteria to do mimic a "SUMIF" > with more than one possible criteria. [quoted text clipped - 5 lines] > an > easy way to do this? {=SUM((C3:C15<>"X")*(C3:C15<>"Y")*(D3:D15))} FormulaArray {=SUM((C3:C15<>"X")*(C3:C15<>"Y")*INDIRECT(B3&"3:"&B3&"15"))} FormulaArray, having in B3: D | E | K | ... Bruno > {=SUM((C3:C15<>"X")*(C3:C15<>"Y")*(D3:D15))} > FormulaArray [quoted text clipped - 3 lines] > > Bruno You can avoid FormulaArray mode replacing SUM with SUMPRODUCT. Bruno Thanks for the info! That indirect function will definitely come in handy. > > {=SUM((C3:C15<>"X")*(C3:C15<>"Y")*(D3:D15))} > > FormulaArray [quoted text clipped - 8 lines] > > Bruno
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.