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.