software

Can the 'criteria' of a SUMIF function allow boolean rules (is this
wording right? )

for example:

=SUMIF(A2:A23,A29 OR A30 OR A31 OR A32 OR A33 OR A34,D223)

baring in mind that the above formula doesn't work.

I have two tables:

1) A3:E26 holds my data
2) A30:A35 holds the names that I wish to sum--
Daminc
------------------------------------------------------------------------
Daminc's Profile: www.excelforum.com/member.php...oamp;userid=27074
View this thread: www.excelforum.com/showthread...hreadid=538372I find your description a bit confusing, but maybe this is what you want

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A26,A30:A35,0))),D226)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;Damincquot; gt; wrote in
message news
gt;
gt; Can the 'criteria' of a SUMIF function allow boolean rules (is this
gt; wording right? )
gt;
gt; for example:
gt;
gt; =SUMIF(A2:A23,A29 OR A30 OR A31 OR A32 OR A33 OR A34,D223)
gt;
gt; baring in mind that the above formula doesn't work.
gt;
gt; I have two tables:
gt;
gt; 1) A3:E26 holds my data
gt; 2) A30:A35 holds the names that I wish to sum
gt;
gt;
gt; --
gt; Daminc
gt; ------------------------------------------------------------------------
gt; Daminc's Profile:
www.excelforum.com/member.php...oamp;userid=27074
gt; View this thread: www.excelforum.com/showthread...hreadid=538372
gt;

Hi Bob, thanks for your reply

I've tried to interperate the formula:

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A26,A30:A35,0))),D226)

with only a little bit of success.

(MATCH(A2:A26,A30:A35,0))
According to reading the MS help files this would give a number from
1-6 depending on what matches what

gt; MATCH returns the position of the matched value within lookup_array, not
gt; the value itself. For example, MATCH(quot;bquot;,{quot;aquot;,quot;bquot;,quot;cquot;},0) returns 2, the
gt; relative position of quot;bquot; within the array {quot;aquot;,quot;bquot;,quot;cquot;}.

but what I actually see is the numbers 1-27 being returned (at least
that's what I think I see)

ISNUMBER checks to see if there is a number or if it equals 'false'.

I have no idea what the '--' signifies

SUMPRODUCT
gt; The following formula multiplies all the components of the two arrays on
gt; the preceding worksheet and then adds the products #8212; that is, 3*2
gt; 4*7 8*6 6*7 1*5 9*3.
gt;
gt; SUMPRODUCT({3,4;8,6;1,9}, {2,7;6,7;5,3}) equals 156

adds the results together if there is a ';' separating the numbers?

D226 is part of the SUMPRODUCT array but I'm not sure how the formula
has left out the non-required numbers (I assume it has something to do
with the ISNUMBER = False bit but assumptions are never a good idea)

Am I close?--
Daminc
------------------------------------------------------------------------
Daminc's Profile: www.excelforum.com/member.php...oamp;userid=27074
View this thread: www.excelforum.com/showthread...hreadid=538372What is happening here is that it is checking the range A2:A26 against the
array of values in A30:A35. If any of these match A2, A3, etc., this returns
a TRUE which -- coerces to a 1. The resultant array of 1/0 in A2:A26 is
multiplied by the values in D226.

As I said, I didn't find your explanation clear, so I made a few
assumptions, most critically that you wanted to check A2:A26 against an
array of values in A30:A35.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;Damincquot; gt; wrote in
message news
gt;
gt; Hi Bob, thanks for your reply
gt;
gt; I've tried to interperate the formula:
gt;
gt; =SUMPRODUCT(--(ISNUMBER(MATCH(A2:A26,A30:A35,0))),D226)
gt;
gt; with only a little bit of success.
gt;
gt; (MATCH(A2:A26,A30:A35,0))
gt; According to reading the MS help files this would give a number from
gt; 1-6 depending on what matches what
gt;
gt; gt; MATCH returns the position of the matched value within lookup_array, not
gt; gt; the value itself. For example, MATCH(quot;bquot;,{quot;aquot;,quot;bquot;,quot;cquot;},0) returns 2, the
gt; gt; relative position of quot;bquot; within the array {quot;aquot;,quot;bquot;,quot;cquot;}.
gt;
gt; but what I actually see is the numbers 1-27 being returned (at least
gt; that's what I think I see)
gt;
gt; ISNUMBER checks to see if there is a number or if it equals 'false'.
gt;
gt; I have no idea what the '--' signifies
gt;
gt; SUMPRODUCT
gt; gt; The following formula multiplies all the components of the two arrays on
gt; gt; the preceding worksheet and then adds the products #8212; that is, 3*2
gt; gt; 4*7 8*6 6*7 1*5 9*3.
gt; gt;
gt; gt; SUMPRODUCT({3,4;8,6;1,9}, {2,7;6,7;5,3}) equals 156
gt;
gt; adds the results together if there is a ';' separating the numbers?
gt;
gt; D226 is part of the SUMPRODUCT array but I'm not sure how the formula
gt; has left out the non-required numbers (I assume it has something to do
gt; with the ISNUMBER = False bit but assumptions are never a good idea)
gt;
gt; Am I close?
gt;
gt;
gt; --
gt; Daminc
gt; ------------------------------------------------------------------------
gt; Daminc's Profile:
www.excelforum.com/member.php...oamp;userid=27074
gt; View this thread: www.excelforum.com/showthread...hreadid=538372
gt;

Your assumptions were correct but two things still confuse me:

1) Where/When does the addition take place?
2) What does the '--' signify?--
Daminc
------------------------------------------------------------------------
Daminc's Profile: www.excelforum.com/member.php...oamp;userid=27074
View this thread: www.excelforum.com/showthread...hreadid=538372Perhaps this will explain it all
www.xldynamic.com/source/xld.SUMPRODUCT.html

---
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

quot;Damincquot; gt; wrote in
message news
gt;
gt; Your assumptions were correct but two things still confuse me:
gt;
gt; 1) Where/When does the addition take place?
gt; 2) What does the '--' signify?
gt;
gt;
gt; --
gt; Daminc
gt; ------------------------------------------------------------------------
gt; Daminc's Profile:
www.excelforum.com/member.php...oamp;userid=27074
gt; View this thread: www.excelforum.com/showthread...hreadid=538372
gt;

Yikes

I'm going to have to print that off and read away from work and then
start exploring the rest of that site

Cheers Bob--
Daminc
------------------------------------------------------------------------
Daminc's Profile: www.excelforum.com/member.php...oamp;userid=27074
View this thread: www.excelforum.com/showthread...hreadid=538372
Thank you Bob.

I've just read this bit of the document:

The value is obtained with
=SUMPRODUCT((A1:A10=quot;Fordquot;)*(B1:B10=quot;Junequot;)*(C1:C1 0))

and it reminded me of the time I had to learn Boolean Algerbra back in
the late '80's. The above is similar to the operations of an AND gate.

This is excellent because I can now actually understand the concept
which will help me remember it.

Again, I thank you --
Daminc
------------------------------------------------------------------------
Daminc's Profile: www.excelforum.com/member.php...oamp;userid=27074
View this thread: www.excelforum.com/showthread...hreadid=538372Glad it helped Daminc.

Bob

quot;Damincquot; gt; wrote in
message news
gt;
gt; Thank you Bob.
gt;
gt; I've just read this bit of the document:
gt;
gt; The value is obtained with
gt; =SUMPRODUCT((A1:A10=quot;Fordquot;)*(B1:B10=quot;Junequot;)*(C1:C1 0))
gt;
gt; and it reminded me of the time I had to learn Boolean Algerbra back in
gt; the late '80's. The above is similar to the operations of an AND gate.
gt;
gt; This is excellent because I can now actually understand the concept
gt; which will help me remember it.
gt;
gt; Again, I thank you
gt;
gt;
gt; --
gt; Daminc
gt; ------------------------------------------------------------------------
gt; Daminc's Profile:
www.excelforum.com/member.php...oamp;userid=27074
gt; View this thread: www.excelforum.com/showthread...hreadid=538372
gt;

Hi Bob, sorry to bother you but I just want to know if I understand this
correctly:

With regards to the double unary operator '--' (weird name )
Is it just a minus-minus equals a plus which then forces the False or
True into a '0' or '1' respectively?--
Daminc
------------------------------------------------------------------------
Daminc's Profile: www.excelforum.com/member.php...oamp;userid=27074
View this thread: www.excelforum.com/showthread...hreadid=538372