Conditional sum in an array

[Edward Wang]

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.

 

[ShaneDevenshire]

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?

 

[Edward Wang]

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

 

[Tom Hutchins]

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",INDIRECT(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

 

[Tom Hutchins]

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$20)),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

 

[Tom Hutchins]

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]

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

 

[Edward Wang]

Hi Tom,

You are genius! And it is really I want!

Thanks,
Edward

 

[Teethless mama]

Try this formula. It doesn't required ctrl+shift+enter. Just press ENTER

=SUMPRODUCT((A2:A20="cond1")*(B1:Z1="cond2")*B2:Z20)

 

[Edward Wang]

Another Genius! It Works!

Try this formula. It doesn't required ctrl+shift+enter. Just press ENTER

=SUMPRODUCT((A2:A20="cond1")*(B1:Z1="cond2")*B2:Z20)

 

[Teethless mama]

You're Welcome!

Another Genius! It Works!

 

[Ashish Mathur]

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