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

D

Danielle

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

Bernard Liengme

D

Danielle

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

Danielle

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

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

Danielle

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

Can this be done with SUM ARRAY formulas?

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

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

Ashish Mathur

Hi,

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

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

Regards,

Ashish Mathur
 
Top