|
SUM Arrays needing an "OR" statment (but no duplicates)
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. |
SUM Arrays needing an "OR" statment (but no duplicates)
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. |
SUM Arrays needing an "OR" statment (but no duplicates)
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. |
SUM Arrays needing an "OR" statment (but no duplicates)
--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. |
SUM Arrays needing an "OR" statment (but no duplicates)
=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. |
SUM Arrays needing an "OR" statment (but no duplicates)
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. |
SUM Arrays needing an "OR" statment (but no duplicates)
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. |
SUM Arrays needing an "OR" statment (but no duplicates)
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. |
| All times are GMT +1. The time now is 03:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com