I am using a sum array (ctrl+shift+enter).. and I have a formula where i want
to test condition but use an "OR" statment.

ie: if title="EA" or class take is "Word", sum how many?

{=SUM((A8:A13="EA")+(B8:B13="Word")) but this equation totals duplicates
too. I only want it to count once, if in one row has either EA or Word.

A few things to note:
1) AND and OR do not work with array formulas
2) The operators * and + replace them; but * is AND so you need
=SUM((A8:A13="EA")*(B8:B13="Word"))
3) All Excel users should get to know SUMPRODUCT.
=SUMPRODUCT(--(A8:A13="EA"), --(B8:B13="Word"))
can be confirmed with simple ENTER - it is not an array formula
For details see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

This site explains the "--":
http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Danielle" wrote in message
...
I am using a sum array (ctrl+shift+enter).. and I have a formula where i
want
to test condition but use an "OR" statment.

ie: if title="EA" or class take is "Word", sum how many?

{=SUM((A8:A13="EA")+(B8:B13="Word")) but this equation totals
duplicates
too. I only want it to count once, if in one row has either EA or Word.

I am familiar with Sumproduct but I find for beginners, that SUM arrays are
easier to understand. The syntax is easier to follow. (Or dumn down - not
having to explain the "--".)

with regards to 2) I agree your formula works with "*" And so does "+" BUT
I am unable to get it to not count duplicates. I basically am wondering it
this is possible at all.

=SUM((A8:A13="EA")*(B8:B13="Word")) CORRECT
=SUM((A8:A13="EA")+ (B8:B13="Word")) in correct (counts duplicates in an OR
subject)


"Bernard Liengme" wrote:

A few things to note:
1) AND and OR do not work with array formulas
2) The operators * and + replace them; but * is AND so you need
=SUM((A8:A13="EA")*(B8:B13="Word"))
3) All Excel users should get to know SUMPRODUCT.
=SUMPRODUCT(--(A8:A13="EA"), --(B8:B13="Word"))
can be confirmed with simple ENTER - it is not an array formula
For details see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

This site explains the "--":
http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Danielle" wrote in message
...
I am using a sum array (ctrl+shift+enter).. and I have a formula where i
want
to test condition but use an "OR" statment.

ie: if title="EA" or class take is "Word", sum how many?

{=SUM((A8:A13="EA")+(B8:B13="Word")) but this equation totals
duplicates
too. I only want it to count once, if in one row has either EA or Word.

--ALSO:

Even if I try the sumproduct: if counts all events even when using the "or"
feature.

ROLE Class Taken
EA Word Count as 1
Staff Word 1
EA Excel 1
Staff Excel 0
Ea Word Count as 1
Staff Word 1

The total I want is: 5. But if I use sumproduct of sum arrays I get 7.
It counts item 1 and item 5 incorrectly.

"Danielle" wrote:

I am familiar with Sumproduct but I find for beginners, that SUM arrays are
easier to understand. The syntax is easier to follow. (Or dumn down - not
having to explain the "--".)

with regards to 2) I agree your formula works with "*" And so does "+" BUT
I am unable to get it to not count duplicates. I basically am wondering it
this is possible at all.

=SUM((A8:A13="EA")*(B8:B13="Word")) CORRECT
=SUM((A8:A13="EA")+ (B8:B13="Word")) in correct (counts duplicates in an OR
subject)


"Bernard Liengme" wrote:

A few things to note:
1) AND and OR do not work with array formulas
2) The operators * and + replace them; but * is AND so you need
=SUM((A8:A13="EA")*(B8:B13="Word"))
3) All Excel users should get to know SUMPRODUCT.
=SUMPRODUCT(--(A8:A13="EA"), --(B8:B13="Word"))
can be confirmed with simple ENTER - it is not an array formula
For details see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

This site explains the "--":
http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Danielle" wrote in message
...
I am using a sum array (ctrl+shift+enter).. and I have a formula where i
want
to test condition but use an "OR" statment.

ie: if title="EA" or class take is "Word", sum how many?

{=SUM((A8:A13="EA")+(B8:B13="Word")) but this equation totals
duplicates
too. I only want it to count once, if in one row has either EA or Word.

=SUMPRODUCT((A8:A13="EA")+(B8:B13="Word"))-SUMPRODUCT(--(A8:A13="EA"),--(B8:
B13="Word"))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Danielle" wrote in message
...
--ALSO:

Even if I try the sumproduct: if counts all events even when using the
"or"
feature.

ROLE Class Taken
EA Word Count as 1
Staff Word 1
EA Excel 1
Staff Excel 0
Ea Word Count as 1
Staff Word 1

The total I want is: 5. But if I use sumproduct of sum arrays I get 7.
It counts item 1 and item 5 incorrectly.

"Danielle" wrote:

I am familiar with Sumproduct but I find for beginners, that SUM arrays
are
easier to understand. The syntax is easier to follow. (Or dumn down -
not
having to explain the "--".)

with regards to 2) I agree your formula works with "*" And so does "+"
BUT
I am unable to get it to not count duplicates. I basically am wondering
it
this is possible at all.

=SUM((A8:A13="EA")*(B8:B13="Word")) CORRECT
=SUM((A8:A13="EA")+ (B8:B13="Word")) in correct (counts duplicates in
an OR
subject)


"Bernard Liengme" wrote:

A few things to note:
1) AND and OR do not work with array formulas
2) The operators * and + replace them; but * is AND so you need
=SUM((A8:A13="EA")*(B8:B13="Word"))
3) All Excel users should get to know SUMPRODUCT.
=SUMPRODUCT(--(A8:A13="EA"), --(B8:B13="Word"))
can be confirmed with simple ENTER - it is not an array formula
For details see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

This site explains the "--":
http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Danielle" wrote in message
...
I am using a sum array (ctrl+shift+enter).. and I have a formula
where i
want
to test condition but use an "OR" statment.

ie: if title="EA" or class take is "Word", sum how many?

{=SUM((A8:A13="EA")+(B8:B13="Word")) but this equation totals
duplicates
too. I only want it to count once, if in one row has either EA or
Word.

Thank you - thank you... one last question.

Can this be done with SUM ARRAY formulas?

ie: =SUM((A8:A13="EA")+ (B8:B13="Word")) - ?????

"Bob Phillips" wrote:

=SUMPRODUCT((A8:A13="EA")+(B8:B13="Word"))-SUMPRODUCT(--(A8:A13="EA"),--(B8:
B13="Word"))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Danielle" wrote in message
...
--ALSO:

Even if I try the sumproduct: if counts all events even when using the
"or"
feature.

ROLE Class Taken
EA Word Count as 1
Staff Word 1
EA Excel 1
Staff Excel 0
Ea Word Count as 1
Staff Word 1

The total I want is: 5. But if I use sumproduct of sum arrays I get 7.
It counts item 1 and item 5 incorrectly.

"Danielle" wrote:

I am familiar with Sumproduct but I find for beginners, that SUM arrays
are
easier to understand. The syntax is easier to follow. (Or dumn down -
not
having to explain the "--".)

with regards to 2) I agree your formula works with "*" And so does "+"
BUT
I am unable to get it to not count duplicates. I basically am wondering
it
this is possible at all.

=SUM((A8:A13="EA")*(B8:B13="Word")) CORRECT
=SUM((A8:A13="EA")+ (B8:B13="Word")) in correct (counts duplicates in
an OR
subject)


"Bernard Liengme" wrote:

A few things to note:
1) AND and OR do not work with array formulas
2) The operators * and + replace them; but * is AND so you need
=SUM((A8:A13="EA")*(B8:B13="Word"))
3) All Excel users should get to know SUMPRODUCT.
=SUMPRODUCT(--(A8:A13="EA"), --(B8:B13="Word"))
can be confirmed with simple ENTER - it is not an array formula
For details see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

This site explains the "--":
http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Danielle" wrote in message
...
I am using a sum array (ctrl+shift+enter).. and I have a formula
where i
want
to test condition but use an "OR" statment.

ie: if title="EA" or class take is "Word", sum how many?

{=SUM((A8:A13="EA")+(B8:B13="Word")) but this equation totals
duplicates
too. I only want it to count once, if in one row has either EA or
Word.

Of course

=SUM((A8:A13="EA")+(B8:B13="Word"))
-SUM((A8:A13="EA")*(B8:B13="Word"))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Danielle" wrote in message
...
Thank you - thank you... one last question.

Can this be done with SUM ARRAY formulas?

ie: =SUM((A8:A13="EA")+ (B8:B13="Word")) - ?????

"Bob Phillips" wrote:


=SUMPRODUCT((A8:A13="EA")+(B8:B13="Word"))-SUMPRODUCT(--(A8:A13="EA"),--(B8:
B13="Word"))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Danielle" wrote in message
...
--ALSO:

Even if I try the sumproduct: if counts all events even when using
the
"or"
feature.

ROLE Class Taken
EA Word Count as 1
Staff Word 1
EA Excel 1
Staff Excel 0
Ea Word Count as 1
Staff Word 1

The total I want is: 5. But if I use sumproduct of sum arrays I get
7.
It counts item 1 and item 5 incorrectly.

"Danielle" wrote:

I am familiar with Sumproduct but I find for beginners, that SUM
arrays
are
easier to understand. The syntax is easier to follow. (Or dumn
down -
not
having to explain the "--".)

with regards to 2) I agree your formula works with "*" And so does
"+"
BUT
I am unable to get it to not count duplicates. I basically am
wondering
it
this is possible at all.

=SUM((A8:A13="EA")*(B8:B13="Word")) CORRECT
=SUM((A8:A13="EA")+ (B8:B13="Word")) in correct (counts duplicates
in
an OR
subject)


"Bernard Liengme" wrote:

A few things to note:
1) AND and OR do not work with array formulas
2) The operators * and + replace them; but * is AND so you need
=SUM((A8:A13="EA")*(B8:B13="Word"))
3) All Excel users should get to know SUMPRODUCT.
=SUMPRODUCT(--(A8:A13="EA"), --(B8:B13="Word"))
can be confirmed with simple ENTER - it is not an array formula
For details see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

This site explains the "--":
http://mcgimpsey.com/excel/formulae/doubleneg.html

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Danielle" wrote in message
...
I am using a sum array (ctrl+shift+enter).. and I have a formula
where i
want
to test condition but use an "OR" statment.

ie: if title="EA" or class take is "Word", sum how many?

{=SUM((A8:A13="EA")+(B8:B13="Word")) but this equation totals
duplicates
too. I only want it to count once, if in one row has either EA
or
Word.

Hi,

You may try the following array formula (Ctrl+Shift+Enter)

=sum(if((A8:A13="EA")+(B8:B13="Word"),sum_range))

Regards,

Ashish Mathur

"Danielle" wrote:

I am using a sum array (ctrl+shift+enter).. and I have a formula where i want
to test condition but use an "OR" statment.

ie: if title="EA" or class take is "Word", sum how many?

{=SUM((A8:A13="EA")+(B8:B13="Word")) but this equation totals duplicates
too. I only want it to count once, if in one row has either EA or Word.