Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
ARRAY Formula
I need to count the number of times that 10 occurs at the same time in two
columns: A B 1 3 4 2 10 6 3 10 10 4 5 10 5 10 10 In this example it happens twice so I would want my SUM(IF...) formula to total 2. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
ARRAY Formula
On Aug 25, 8:24 pm, Craig wrote:
I need to count the number of times that 10 occurs at the same time in two columns: A B 1 3 4 2 10 6 3 10 10 4 5 10 5 10 10 In this example it happens twice so I would want my SUM(IF...) formula to total 2. =SUMPRODUCT(--($A$1:$A$5=10),--($B$1:$B$5=10)) Ken Johnson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
ARRAY Formula
Not SUM(IF_) but try:
=SUMPRODUCT(--(A1:A5=10),--(B1:B5=10)) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Craig" wrote in message ... I need to count the number of times that 10 occurs at the same time in two columns: A B 1 3 4 2 10 6 3 10 10 4 5 10 5 10 10 In this example it happens twice so I would want my SUM(IF...) formula to total 2. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
ARRAY Formula
Thanks! I tried it on my example and it works. However, I do not understand
how the code works. I looked up SUMPRODUCT help and it does not show the -- signs. Not being a real programmer, could you explain this coding to me? I was just trying with an example. I need to eventually figure out a long couple of columns such that one must contain a 10 and the other either a 10 or a 5. It could be 10,10 or 10, 5 or 5,10. Any of these results registering as a one then summed up. Any thoughts? "Sandy Mann" wrote: Not SUM(IF_) but try: =SUMPRODUCT(--(A1:A5=10),--(B1:B5=10)) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Craig" wrote in message ... I need to count the number of times that 10 occurs at the same time in two columns: A B 1 3 4 2 10 6 3 10 10 4 5 10 5 10 10 In this example it happens twice so I would want my SUM(IF...) formula to total 2. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
ARRAY Formula
Thank you very much! Good article on SUMPRODUCT!! I did think about things
a bit and found that =SUMPRODUCT(--(G6:G54=10),--(H6:H54=10))+SUMPRODUCT(--(G6:G54=10),--(H6:H54=5))+SUMPRODUCT(--(G6:G54=5),--(H6:H54=10)) seems to work since 5,5 is not a legal combination to be considered. Again thanks! Not sure how to call this one answered but would be more than happy to close it out if I need to do so.... "Sandy Mann" wrote: "Craig" wrote in message ... Thanks! I tried it on my example and it works. However, I do not understand how the code works Bob Philips explains how to use SUMPRODUCT() like this far better than I ever could: http://www.xldynamic.com/source/xld.SUMPRODUCT.html I was just trying with an example. I need to eventually figure out a long couple of columns such that one must contain a 10 and the other either a 10 or a 5. It could be 10,10 or 10, 5 or 5,10. Any of these results registering as a one then summed up. Any thoughts? In SUMPRODUCT() formulas * (multiplication) works like an AND() and + works like on OR so it follows that =SUMPRODUCT(((A1:A10=5)+(A1:A10=10)),((B1:B10=5)+( B1:B10=10))) will return a count of the number of matches or 5 or 10. Note that this will also include 5 & 5, if that is not acceptable then read Bob's site or post back. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Craig" wrote in message ... Thanks! I tried it on my example and it works. However, I do not understand how the code works. I looked up SUMPRODUCT help and it does not show the -- signs. Not being a real programmer, could you explain this coding to me? I was just trying with an example. I need to eventually figure out a long couple of columns such that one must contain a 10 and the other either a 10 or a 5. It could be 10,10 or 10, 5 or 5,10. Any of these results registering as a one then summed up. Any thoughts? "Sandy Mann" wrote: Not SUM(IF_) but try: =SUMPRODUCT(--(A1:A5=10),--(B1:B5=10)) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Craig" wrote in message ... I need to count the number of times that 10 occurs at the same time in two columns: A B 1 3 4 2 10 6 3 10 10 4 5 10 5 10 10 In this example it happens twice so I would want my SUM(IF...) formula to total 2. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
ARRAY Formula
"Craig" wrote in message
... Thank you very much! Good article on SUMPRODUCT!! I did think about things a bit and found that =SUMPRODUCT(--(G6:G54=10),--(H6:H54=10))+SUMPRODUCT(--(G6:G54=10),--(H6:H54=5))+SUMPRODUCT(--(G6:G54=5),--(H6:H54=10)) Good thinking, you seem to assimilated the article very well. -- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
ARRAY Formula
On Mon, 25 Aug 2008 22:31:11 +0100, "Sandy Mann"
wrote: "Craig" wrote in message ... Thanks! I tried it on my example and it works. However, I do not understand how the code works Bob Philips explains how to use SUMPRODUCT() like this far better than I ever could: http://www.xldynamic.com/source/xld.SUMPRODUCT.html I was just trying with an example. I need to eventually figure out a long couple of columns such that one must contain a 10 and the other either a 10 or a 5. It could be 10,10 or 10, 5 or 5,10. Any of these results registering as a one then summed up. Any thoughts? In SUMPRODUCT() formulas * (multiplication) works like an AND() and + works like on OR so it follows that =SUMPRODUCT(((A1:A10=5)+(A1:A10=10)),((B1:B10=5)+ (B1:B10=10))) will return a count of the number of matches or 5 or 10. Note that this will also include 5 & 5, if that is not acceptable then read Bob's site or post back. The statement that "+ works like OR" is not generally true, only if the two (or more) conditions are strictly disjoint. If they are partly overlapping, as can be the case if variable data is involved then you have to be careful. Example: (A1:A10=5)+(A1:A10X) in a formula like the one above will give wrong results if X, that can be a cell reference, holds a value less than 5. To get a real OR functionality you can write the above as - -( ( (A1:A10=5) + (A1:A10X) ) 0 ) Lars-Åke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help with formula (IF or perhaps array) | New Users to Excel | |||
SUM+IF Array formula | Excel Worksheet Functions | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
Can this be done using an array formula ? | Excel Worksheet Functions | |||
Array Formula - using LEFT("text",4) in formula | Excel Worksheet Functions |