Sumif with multiple criteria Pt 2

[Steve]

OK If sumif wont work what will..

Range A1:F1 is yes or blank
Range A2:F2 is number positive or negative
G2 sum of negative nos where A2:F2 is neg & A1:F1 is yes
H2 sum of positive nos where A2:F2 is pos & A1:F1 is yes

Thanks

 

[Bob Phillips]

Steve,

SUMPRODUCT AS the man said

G2: =SUMPRODUCT((A1:F1="yes")*(A2:F2<0),(A2:F2))
H2: =SUMPRODUCT((A1:F1="yes")*(A2:F2>0),(A2:F2))
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Alan]

In G2

=SUMPRODUCT((A1:F1="Yes")*(A2:F2<0)*(A2:F2))

In H2

=SUMPRODUCT((A1:F1="Yes")*(A2:F2>0)*(A2:F2))

Alan

 

[J.E. McGimpsey]

One way:

G2: =SUMPRODUCT(--(A1:F1="Yes"),--(A2:F2<0),A2:F2)
H2: =SUMPRODUCT(--(A1:F1="Yes"),--(A2:F2>0),A2:F2)

 

[Alan]

J.E.
I've never seen SUMPRODUCT used like that, could you please explain how
it works, I cant grasp the significance of the double minus signs,
Thanks,
Alan.

 

[Anon]

The double minus signs simply convert booleans to numbers. For example, if
A1="Yes", the result is the boolean value TRUE. The SUMPRODUCT function,
written with commas between its parameters, requires those parameters to be
numbers, not boolean. So the double minus sign is one way of converting TRUE
to 1 (and FALSE to 0). (The first minus sign makes it negative and the
second makes it positive again.) The same could be achieved by multiplying
by 1 as is sometimes seen: SUMPRODUCT((A1:F1="Yes")*1 ......

If SUMPRODUCT is written with multiplication signs rather than commas, in
effect this conversion happens automatically:
=SUMPRODUCT((A1:F1="Yes")*(A2:F2<0)*A2:F2)

 

[Alan]

Thanks very much for that, we live and learn!
Regards,
Alan.

 

[J.E. McGimpsey]

But, as Dave Braden and others have recently discovered and posted
to the excel groups, multiplying the arrays first, then taking the
SUMPRODUCT() is about 20% slower than using the comma notation.

 

[RagDyer]

YES, but which form is the quickest and easiest and least burdensome for XL
to process ?
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

"Anon" <none> wrote in message The double minus signs simply convert booleans to numbers. For example, if
A1="Yes", the result is the boolean value TRUE. The SUMPRODUCT function,
written with commas between its parameters, requires those parameters to be
numbers, not boolean. So the double minus sign is one way of converting TRUE
to 1 (and FALSE to 0). (The first minus sign makes it negative and the
second makes it positive again.) The same could be achieved by multiplying
by 1 as is sometimes seen: SUMPRODUCT((A1:F1="Yes")*1 ......

If SUMPRODUCT is written with multiplication signs rather than commas, in
effect this conversion happens automatically:
=SUMPRODUCT((A1:F1="Yes")*(A2:F2<0)*A2:F2)