SUM Arrays needing an "OR" statment (but no duplicates)

[Guest]

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.

 

[Bernard Liengme]

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

 

[Guest]

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)

 

[Guest]

--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.

 

[Bob Phillips]

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

 

[Guest]

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]

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)

 

[Guest]

Hi,

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

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

Regards,

Ashish Mathur