Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional sum in an array
I need to make a flexible sum in an array( not a single column or row). Say:
1. sum range: B2:Z20 2. Criteria1 range: B1:Z1 Criteria 2 range: A2 A20 Any data in the sum range will be summed up if the conditions meet in the the criteria ranges. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional sum in an array
Hi,
You need to elaborate. In the range A1:A20 are their multiple conditions and do they apply to all the range in B2:Z20 or is it on a row by row basis? What are the criteria, text, dates, number, are you testing for ,<, =, =, <, = or are you testing for Errors or text, dates or number contained within a string? -- Thanks, Shane Devenshire "Edward Wang" wrote: I need to make a flexible sum in an array( not a single column or row). Say: 1. sum range: B2:Z20 2. Criteria1 range: B1:Z1 Criteria 2 range: A2 A20 Any data in the sum range will be summed up if the conditions meet in the the criteria ranges. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional sum in an array
Hi Shane,
To your second question first: it is text. The criteria will like: ="Design" To your first question: there are two criteria ranges A2:A20, and B1:Z1. the criteria of each will be very simple just like above. by {A2:A20} criteria range selection : one or more columns of sum range {B2:Z20} will be selected By {B1:Z1} criteria range selection: one or more rows of sum range {B2:Z20} will be selected The data of intersections of sum range {B2:Z20} will be summed up. Hope this will be more clear. Regards, Edward "ShaneDevenshire" wrote: Hi, You need to elaborate. In the range A1:A20 are their multiple conditions and do they apply to all the range in B2:Z20 or is it on a row by row basis? What are the criteria, text, dates, number, are you testing for ,<, =, =, <, = or are you testing for Errors or text, dates or number contained within a string? -- Thanks, Shane Devenshire "Edward Wang" wrote: I need to make a flexible sum in an array( not a single column or row). Say: 1. sum range: B2:Z20 2. Criteria1 range: B1:Z1 Criteria 2 range: A2 A20 Any data in the sum range will be summed up if the conditions meet in the the criteria ranges. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional sum in an array
Here is one way (until Shane or someone sends a slick array formula to do it
all in one step)... In an empty area below your data (B30, for example), enter this formula: =IF(AND(INDIRECT(ADDRESS(ROW(B2),1))="Cond2",INDIR ECT(ADDRESS(1,COLUMN(B2)))="Cond1"),B2,0) Copy it down, then across until it is the same size (19 rows and 25 columns) as your sum range. You could hide these rows if desired. Change Cond1 and Cond2 to whatever you need. In another cell (say B50), enter this formula, which is the answer: =SUM(B30:Z48) Hope this helps, Hutch "Edward Wang" wrote: Hi Shane, To your second question first: it is text. The criteria will like: ="Design" To your first question: there are two criteria ranges A2:A20, and B1:Z1. the criteria of each will be very simple just like above. by {A2:A20} criteria range selection : one or more columns of sum range {B2:Z20} will be selected By {B1:Z1} criteria range selection: one or more rows of sum range {B2:Z20} will be selected The data of intersections of sum range {B2:Z20} will be summed up. Hope this will be more clear. Regards, Edward "ShaneDevenshire" wrote: Hi, You need to elaborate. In the range A1:A20 are their multiple conditions and do they apply to all the range in B2:Z20 or is it on a row by row basis? What are the criteria, text, dates, number, are you testing for ,<, =, =, <, = or are you testing for Errors or text, dates or number contained within a string? -- Thanks, Shane Devenshire "Edward Wang" wrote: I need to make a flexible sum in an array( not a single column or row). Say: 1. sum range: B2:Z20 2. Criteria1 range: B1:Z1 Criteria 2 range: A2 A20 Any data in the sum range will be summed up if the conditions meet in the the criteria ranges. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional sum in an array
Here's a better solution. In the empty area blow your data (assume B30
again), enter this array formula: {=SUM(IF(B$1="Cond1",($A$2:$A$20="Cond2")*(B$2:B$2 0)),0)} This is an array formula. Hit Ctrl-Shift-Enter instead of Enter. If you do it correctly, Excel will wrap curly brackets {} around your formula (don't type them yourself.) Copy the formula across through column Z. The solution is the sum of B30:Z30. We have compressed rows 30-48 from my earlier solution into a single row. Hutch "Edward Wang" wrote: Hi Shane, To your second question first: it is text. The criteria will like: ="Design" To your first question: there are two criteria ranges A2:A20, and B1:Z1. the criteria of each will be very simple just like above. by {A2:A20} criteria range selection : one or more columns of sum range {B2:Z20} will be selected By {B1:Z1} criteria range selection: one or more rows of sum range {B2:Z20} will be selected The data of intersections of sum range {B2:Z20} will be summed up. Hope this will be more clear. Regards, Edward "ShaneDevenshire" wrote: Hi, You need to elaborate. In the range A1:A20 are their multiple conditions and do they apply to all the range in B2:Z20 or is it on a row by row basis? What are the criteria, text, dates, number, are you testing for ,<, =, =, <, = or are you testing for Errors or text, dates or number contained within a string? -- Thanks, Shane Devenshire "Edward Wang" wrote: I need to make a flexible sum in an array( not a single column or row). Say: 1. sum range: B2:Z20 2. Criteria1 range: B1:Z1 Criteria 2 range: A2 A20 Any data in the sum range will be summed up if the conditions meet in the the criteria ranges. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional sum in an array
This is just embarassing. Here is an array formula that does it all:
{=SUM(IF(($B$1:$Z$1="Cond1")*($A$2:$A$26="Cond2"), $B$2:$Z$26,0))} Hutch "Edward Wang" wrote: Hi Shane, To your second question first: it is text. The criteria will like: ="Design" To your first question: there are two criteria ranges A2:A20, and B1:Z1. the criteria of each will be very simple just like above. by {A2:A20} criteria range selection : one or more columns of sum range {B2:Z20} will be selected By {B1:Z1} criteria range selection: one or more rows of sum range {B2:Z20} will be selected The data of intersections of sum range {B2:Z20} will be summed up. Hope this will be more clear. Regards, Edward "ShaneDevenshire" wrote: Hi, You need to elaborate. In the range A1:A20 are their multiple conditions and do they apply to all the range in B2:Z20 or is it on a row by row basis? What are the criteria, text, dates, number, are you testing for ,<, =, =, <, = or are you testing for Errors or text, dates or number contained within a string? -- Thanks, Shane Devenshire "Edward Wang" wrote: I need to make a flexible sum in an array( not a single column or row). Say: 1. sum range: B2:Z20 2. Criteria1 range: B1:Z1 Criteria 2 range: A2 A20 Any data in the sum range will be summed up if the conditions meet in the the criteria ranges. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional sum in an array
Hi Tom,
Thank your quick answering! It works! However, there are lots of such kind of conditional sums required in the table, actually it will be another small array based on the raw data. If I use this solution, there are too much spaces used in the table, and looks not clean & beautiful. Thanks, Edward |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional sum in an array
Hi Tom,
You are genius! And it is really I want! Thanks, Edward "Tom Hutchins" wrote: This is just embarassing. Here is an array formula that does it all: {=SUM(IF(($B$1:$Z$1="Cond1")*($A$2:$A$26="Cond2"), $B$2:$Z$26,0))} Hutch "Edward Wang" wrote: Hi Shane, To your second question first: it is text. The criteria will like: ="Design" To your first question: there are two criteria ranges A2:A20, and B1:Z1. the criteria of each will be very simple just like above. by {A2:A20} criteria range selection : one or more columns of sum range {B2:Z20} will be selected By {B1:Z1} criteria range selection: one or more rows of sum range {B2:Z20} will be selected The data of intersections of sum range {B2:Z20} will be summed up. Hope this will be more clear. Regards, Edward "ShaneDevenshire" wrote: Hi, You need to elaborate. In the range A1:A20 are their multiple conditions and do they apply to all the range in B2:Z20 or is it on a row by row basis? What are the criteria, text, dates, number, are you testing for ,<, =, =, <, = or are you testing for Errors or text, dates or number contained within a string? -- Thanks, Shane Devenshire "Edward Wang" wrote: I need to make a flexible sum in an array( not a single column or row). Say: 1. sum range: B2:Z20 2. Criteria1 range: B1:Z1 Criteria 2 range: A2 A20 Any data in the sum range will be summed up if the conditions meet in the the criteria ranges. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional sum in an array
Try this formula. It doesn't required ctrl+shift+enter. Just press ENTER
=SUMPRODUCT((A2:A20="cond1")*(B1:Z1="cond2")*B2:Z2 0) "Edward Wang" wrote: Hi Tom, You are genius! And it is really I want! Thanks, Edward "Tom Hutchins" wrote: This is just embarassing. Here is an array formula that does it all: {=SUM(IF(($B$1:$Z$1="Cond1")*($A$2:$A$26="Cond2"), $B$2:$Z$26,0))} Hutch "Edward Wang" wrote: Hi Shane, To your second question first: it is text. The criteria will like: ="Design" To your first question: there are two criteria ranges A2:A20, and B1:Z1. the criteria of each will be very simple just like above. by {A2:A20} criteria range selection : one or more columns of sum range {B2:Z20} will be selected By {B1:Z1} criteria range selection: one or more rows of sum range {B2:Z20} will be selected The data of intersections of sum range {B2:Z20} will be summed up. Hope this will be more clear. Regards, Edward "ShaneDevenshire" wrote: Hi, You need to elaborate. In the range A1:A20 are their multiple conditions and do they apply to all the range in B2:Z20 or is it on a row by row basis? What are the criteria, text, dates, number, are you testing for ,<, =, =, <, = or are you testing for Errors or text, dates or number contained within a string? -- Thanks, Shane Devenshire "Edward Wang" wrote: I need to make a flexible sum in an array( not a single column or row). Say: 1. sum range: B2:Z20 2. Criteria1 range: B1:Z1 Criteria 2 range: A2 A20 Any data in the sum range will be summed up if the conditions meet in the the criteria ranges. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional sum in an array
Another Genius! It Works!
"Teethless mama" wrote: Try this formula. It doesn't required ctrl+shift+enter. Just press ENTER =SUMPRODUCT((A2:A20="cond1")*(B1:Z1="cond2")*B2:Z2 0) "Edward Wang" wrote: Hi Tom, You are genius! And it is really I want! Thanks, Edward "Tom Hutchins" wrote: This is just embarassing. Here is an array formula that does it all: {=SUM(IF(($B$1:$Z$1="Cond1")*($A$2:$A$26="Cond2"), $B$2:$Z$26,0))} Hutch "Edward Wang" wrote: Hi Shane, To your second question first: it is text. The criteria will like: ="Design" To your first question: there are two criteria ranges A2:A20, and B1:Z1. the criteria of each will be very simple just like above. by {A2:A20} criteria range selection : one or more columns of sum range {B2:Z20} will be selected By {B1:Z1} criteria range selection: one or more rows of sum range {B2:Z20} will be selected The data of intersections of sum range {B2:Z20} will be summed up. Hope this will be more clear. Regards, Edward "ShaneDevenshire" wrote: Hi, You need to elaborate. In the range A1:A20 are their multiple conditions and do they apply to all the range in B2:Z20 or is it on a row by row basis? What are the criteria, text, dates, number, are you testing for ,<, =, =, <, = or are you testing for Errors or text, dates or number contained within a string? -- Thanks, Shane Devenshire "Edward Wang" wrote: I need to make a flexible sum in an array( not a single column or row). Say: 1. sum range: B2:Z20 2. Criteria1 range: B1:Z1 Criteria 2 range: A2 A20 Any data in the sum range will be summed up if the conditions meet in the the criteria ranges. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional sum in an array
You're Welcome!
"Edward Wang" wrote: Another Genius! It Works! "Teethless mama" wrote: Try this formula. It doesn't required ctrl+shift+enter. Just press ENTER =SUMPRODUCT((A2:A20="cond1")*(B1:Z1="cond2")*B2:Z2 0) "Edward Wang" wrote: Hi Tom, You are genius! And it is really I want! Thanks, Edward "Tom Hutchins" wrote: This is just embarassing. Here is an array formula that does it all: {=SUM(IF(($B$1:$Z$1="Cond1")*($A$2:$A$26="Cond2"), $B$2:$Z$26,0))} Hutch "Edward Wang" wrote: Hi Shane, To your second question first: it is text. The criteria will like: ="Design" To your first question: there are two criteria ranges A2:A20, and B1:Z1. the criteria of each will be very simple just like above. by {A2:A20} criteria range selection : one or more columns of sum range {B2:Z20} will be selected By {B1:Z1} criteria range selection: one or more rows of sum range {B2:Z20} will be selected The data of intersections of sum range {B2:Z20} will be summed up. Hope this will be more clear. Regards, Edward "ShaneDevenshire" wrote: Hi, You need to elaborate. In the range A1:A20 are their multiple conditions and do they apply to all the range in B2:Z20 or is it on a row by row basis? What are the criteria, text, dates, number, are you testing for ,<, =, =, <, = or are you testing for Errors or text, dates or number contained within a string? -- Thanks, Shane Devenshire "Edward Wang" wrote: I need to make a flexible sum in an array( not a single column or row). Say: 1. sum range: B2:Z20 2. Criteria1 range: B1:Z1 Criteria 2 range: A2 A20 Any data in the sum range will be summed up if the conditions meet in the the criteria ranges. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional sum in an array
Hi,
Try this array formula (Ctrl+shift+Enter) =sum(if((A2:A20="cond1")*(B1:Z1="cond2"),B2:Z20)) -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Edward Wang" wrote in message ... I need to make a flexible sum in an array( not a single column or row). Say: 1. sum range: B2:Z20 2. Criteria1 range: B1:Z1 Criteria 2 range: A2 A20 Any data in the sum range will be summed up if the conditions meet in the the criteria ranges. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional sum on an array based on another array | Excel Discussion (Misc queries) | |||
Conditional Array Formula | Excel Worksheet Functions | |||
array conditional equation | Excel Worksheet Functions | |||
Conditional Formula - No array | Excel Worksheet Functions | |||
Conditional transpose to Array | Excel Discussion (Misc queries) |