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