ARRAY Formula

[Craig]

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.

 

[Ken Johnson]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Craig]

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]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Craig]

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]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Lars-Åke Aspelin]

Bob Philips explains how to use SUMPRODUCT() like this far better than I
ever could:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html


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:A10>X) 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:A10>X) ) > 0 )

Lars-Åke