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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.