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.
|