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,D2:D23)

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: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=538372

I find your description a bit confusing, but maybe this is what you want

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

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Daminc" wrote in
message ...

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,D2:D23)

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:
http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=538372

Hi Bob, thanks for your reply :)

I've tried to interperate the formula:

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

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?

D2:D26 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: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=538372

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 D2:D26.

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" wrote in
message ...

Hi Bob, thanks for your reply :)

I've tried to interperate the formula:

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

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?

D2:D26 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:
http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=538372

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: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=538372

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" wrote in
message ...

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:
http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=538372

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: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://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="Ford")*(B1:B10="June")*(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: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=538372

Glad it helped Daminc.

Bob

"Daminc" wrote in
message ...

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: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:
http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=538372

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: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=538372

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" wrote in
message ...

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:
http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=538372

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

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


--
Daminc
------------------------------------------------------------------------
Daminc's Profile: http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=538372

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

"Daminc" wrote in
message ...

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

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


--
Daminc
------------------------------------------------------------------------
Daminc's Profile:
http://www.excelforum.com/member.php...o&userid=27074
View this thread: http://www.excelforum.com/showthread...hreadid=538372