ExcelBanter - ARRAY Formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - ARRAY Formula (https://www.excelbanter.com/excel-discussion-misc-queries/200054-array-formula.html)

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.

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

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.

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

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.

"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

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