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.