SUMIF boolean?

[Daminc]

Can the 'criteria' of a SUMIF function allow boolean rules (is thi
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 su

 

[Bob Phillips]

I 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)

 

[Daminc]

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

MATCH returns the position of the matched value within lookup_array, not
the value itself. For example, MATCH("b",{"a","b","c"},0) returns 2, the
relative position of "b" within the array {"a","b","c"}.

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

The following formula multiplies all the components of the two arrays on
the preceding worksheet and then adds the products — that is, 3*2
+ 4*7 + 8*6 + 6*7 + 1*5 + 9*3.

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?

 

[Bob Phillips]

What 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)

 

[Daminc]

Your assumptions were correct but two things still confuse me:

1) Where/When does the addition take place?
2) What does the '--' signify

 

[Bob Phillips]

Perhaps this will explain it all
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

---
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Daminc]

Yikes

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

Cheers Bo

 

[Daminc]

Thank you Bob.

I've just read this bit of the document:

The value is obtained with
=SUMPRODUCT((A1:A10="Ford")*(B1:B10="June")*(C1:C10))

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

 

[Bob Phillips]

Glad it helped Daminc.

Bob

 

[Daminc]

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?

 

[Bob Phillips]

Basically yes that is so. The first - coerces a TRUE to -1, FALSE to 0, the
second - then reverts it to 1 or 0, which is used to do the product part of
SUMPRODUCT.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Daminc]

Hi Bob, on a side note I noticed when I downloaded some of the workbook
from http://www.xldynamic.com that you're the one who created them. Wit
regards to the actual tutorials (e.g
http://www.xldynamic.com/source/xld.SUMPRODUCT.html) if you the actua
writer why don't you put your name to it?

When ever I find a decent tutorial I copy and reformat it onto a wor
doc and put a source URL below the heading. It would also be good t
place whoever wrote it on the doc as well (it might also cover an
copywriting problems that may occur in the future if the subject eve
crops up

 

[Bob Phillips]

I did write it Daminc, but copyright is not an issue IMO.

In later articles, I adopted a different style (see
http://xldynamic.com/source/xld.LastValue.html as an example), where I do
add the author's name (Frank Kabel and I in that example), and I also
include a Word version (I personally prefer Word documents than HTML, even
when the latter are print formatted as I do on that site). The paper on
SUMPRODUCT is on my to-do list to convert in that way, and I also want to
re-write it as it is quite old and could do with a refresh, but my list is
pretty long <G>

Bob