Bob, I am studying your wonderful article on sumproduct. I have this:

A B
1 Make Month
2 Ford June
3 Ford June
4 Ford May
5 Ford June
6 Renault June
7 Renault June
8 BMW June
9 BMW May
10 BMW June

The question is, how many cars were either Fords or were sold in June?
I used
=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June"))
expecting to get 8. Yet, I get 11.
I was expecting + to work like logical or, but it worked like arithmetic
plus.
Did I miss something? Or, how can I fix this to get the expected answer?
James

Don't 4 fords and 7 Junes=11 ?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Zone" wrote in message
...
Bob, I am studying your wonderful article on sumproduct. I have this:

A B
1 Make Month
2 Ford June
3 Ford June
4 Ford May
5 Ford June
6 Renault June
7 Renault June
8 BMW June
9 BMW May
10 BMW June

The question is, how many cars were either Fords or were sold in June?
I used
=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June"))
expecting to get 8. Yet, I get 11.
I was expecting + to work like logical or, but it worked like arithmetic
plus.
Did I miss something? Or, how can I fix this to get the expected answer?
James

Yes, it does, Don. Problem is, there are only 9 cars. So the answer of 11
is not correct for the question as posed. James

"Don Guillett" wrote in message
...
Don't 4 fords and 7 Junes=11 ?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Zone" wrote in message
...
Bob, I am studying your wonderful article on sumproduct. I have this:

A B
1 Make Month
2 Ford June
3 Ford June
4 Ford May
5 Ford June
6 Renault June
7 Renault June
8 BMW June
9 BMW May
10 BMW June

The question is, how many cars were either Fords or were sold in June?
I used
=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June"))
expecting to get 8. Yet, I get 11.
I was expecting + to work like logical or, but it worked like arithmetic
plus.
Did I miss something? Or, how can I fix this to get the expected answer?
James

How about this that assumes that all blanks in a are filled
=MIN(COUNTA(a2:a11),SUMPRODUCT((a2:b11={"Ford","Ju ne"})*1))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Zone" wrote in message
...
Yes, it does, Don. Problem is, there are only 9 cars. So the answer of
11 is not correct for the question as posed. James

"Don Guillett" wrote in message
...
Don't 4 fords and 7 Junes=11 ?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Zone" wrote in message
...
Bob, I am studying your wonderful article on sumproduct. I have this:

A B
1 Make Month
2 Ford June
3 Ford June
4 Ford May
5 Ford June
6 Renault June
7 Renault June
8 BMW June
9 BMW May
10 BMW June

The question is, how many cars were either Fords or were sold in June?
I used
=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June"))
expecting to get 8. Yet, I get 11.
I was expecting + to work like logical or, but it worked like arithmetic
plus.
Did I miss something? Or, how can I fix this to get the expected
answer?
James

I am sure Bob will not mind if I answer:
=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")) -
SUMPRODUCT((A1:A10="Ford")*(B1:B10="June"))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Zone" wrote in message
...
Bob, I am studying your wonderful article on sumproduct. I have this:

A B
1 Make Month
2 Ford June
3 Ford June
4 Ford May
5 Ford June
6 Renault June
7 Renault June
8 BMW June
9 BMW May
10 BMW June

The question is, how many cars were either Fords or were sold in June?
I used
=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June"))
expecting to get 8. Yet, I get 11.
I was expecting + to work like logical or, but it worked like arithmetic
plus.
Did I miss something? Or, how can I fix this to get the expected answer?
James

Or in shorter form
=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")-(A1:A10="Ford")*(B1:B10="June"))
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Zone" wrote in message
...
Bob, I am studying your wonderful article on sumproduct. I have this:

A B
1 Make Month
2 Ford June
3 Ford June
4 Ford May
5 Ford June
6 Renault June
7 Renault June
8 BMW June
9 BMW May
10 BMW June

The question is, how many cars were either Fords or were sold in June?
I used
=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June"))
expecting to get 8. Yet, I get 11.
I was expecting + to work like logical or, but it worked like arithmetic
plus.
Did I miss something? Or, how can I fix this to get the expected answer?
James

Perfect! Thanks, Bernard. Both give the correct answer. Now, this is
something for me to mull over! James

"Bernard Liengme" wrote in message
...
Or in shorter form
=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")-(A1:A10="Ford")*(B1:B10="June"))
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Zone" wrote in message
...
Bob, I am studying your wonderful article on sumproduct. I have this:

A B
1 Make Month
2 Ford June
3 Ford June
4 Ford May
5 Ford June
6 Renault June
7 Renault June
8 BMW June
9 BMW May
10 BMW June

The question is, how many cars were either Fords or were sold in June?
I used
=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June"))
expecting to get 8. Yet, I get 11.
I was expecting + to work like logical or, but it worked like arithmetic
plus.
Did I miss something? Or, how can I fix this to get the expected answer?
James

Zone,

There is an example that explains this, it is because the June Fords get
double counted.

There is also a way to exclude the double-counting

=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June"))-SUMPRODUCT(--(A1:A10="Ford"),--(B1:B10="June"))

I have been posting an improvement on this recently of

=SUMPRODUCT(--(((A1:A10="Ford")+(B1:B10="June"))0))

But I have a better way, which the page is currently due an update to
include, which is

=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="June")))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Zone" wrote in message
...
Bob, I am studying your wonderful article on sumproduct. I have this:

A B
1 Make Month
2 Ford June
3 Ford June
4 Ford May
5 Ford June
6 Renault June
7 Renault June
8 BMW June
9 BMW May
10 BMW June

The question is, how many cars were either Fords or were sold in June?
I used
=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June"))
expecting to get 8. Yet, I get 11.
I was expecting + to work like logical or, but it worked like arithmetic
plus.
Did I miss something? Or, how can I fix this to get the expected answer?
James

Thanks, Bob! I'm sure you were making the point that sumproduct could be
used to get the total instances of cars that were either Fords or sold in
June at that point in the article. But I got stuck on the literal meaning
of "cars that were either Fords or sold in June". Now I can go back to
studying your article! Many thanks, James

"Bob Phillips" wrote in message
...
Zone,

There is an example that explains this, it is because the June Fords get
double counted.

There is also a way to exclude the double-counting

=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June"))-SUMPRODUCT(--(A1:A10="Ford"),--(B1:B10="June"))

I have been posting an improvement on this recently of

=SUMPRODUCT(--(((A1:A10="Ford")+(B1:B10="June"))0))

But I have a better way, which the page is currently due an update to
include, which is

=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="June")))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Zone" wrote in message
...
Bob, I am studying your wonderful article on sumproduct. I have this:

A B
1 Make Month
2 Ford June
3 Ford June
4 Ford May
5 Ford June
6 Renault June
7 Renault June
8 BMW June
9 BMW May
10 BMW June

The question is, how many cars were either Fords or were sold in June?
I used
=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June"))
expecting to get 8. Yet, I get 11.
I was expecting + to work like logical or, but it worked like arithmetic
plus.
Did I miss something? Or, how can I fix this to get the expected answer?
James

"Bob Phillips" wrote...
....
But I have a better way, which the page is currently due an update to
include, which is

=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="June")) )
....

Better how? It uses an extra level of nested function calls.

There are shorter array formulas.

=COUNT(1/((A1:A10="Ford")+(B1:B10="June")))

Oh goodie, Harlan woke up grumpy for a change ...

I think it is better because it is more intuitive IMO, and one extra
function call is rarely going to make any difference.

As to your array alternative, I quote ...

True, but I meant in the sense of avoiding unnecessary follow-up and
responses when the OP failed to enter your SUM formula as an array formula.
Care to check the frequency of OPs failing to catch the 'array formula'
caveats in responses they receive?

'Course if you like the unnecessary addition back & forth . . .

Guess who said that? But I suppose you will see that as an insult in your
inimitable way.

Bob

"Harlan Grove" wrote in message
...
"Bob Phillips" wrote...
...
But I have a better way, which the page is currently due an update to
include, which is

=SUMPRODUCT(SIGN((A1:A10="Ford")+(B1:B10="June") ))
...

Better how? It uses an extra level of nested function calls.

There are shorter array formulas.

=COUNT(1/((A1:A10="Ford")+(B1:B10="June")))

"Bob Phillips" wrote...
....
I think it is better because it is more intuitive IMO, and one extra
function call is rarely going to make any difference.

You once wrote something like that about using N(..) in place of adding 0,
multiplying by 1 or double negating boolean arrays. Not much use of that
idiom anymore.

As to your array alternative, I quote ...
....

Note I didn't SUGGEST its use, only said it's shorter, which is demonstrably
true. There are still some occasions when reducing file size and recalc time
are usefule if not necessary.

"Harlan Grove" wrote in message
...
"Bob Phillips" wrote...

You once wrote something like that about using N(..) in place of adding 0,
multiplying by 1 or double negating boolean arrays. Not much use of that
idiom anymore.


How nice, that you should follow my postings so closely, with impeccable
(you think!) recall.


Note I didn't SUGGEST its use, only said it's shorter, which is
demonstrably true. There are still some occasions when reducing file size
and recalc time are usefule if not necessary.

Yes you did, you said ...

Should have used


=SUMPRODUCT(--D2:D6)

One other way you could write it:
=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="June")0))


"Zone" wrote:

Bob, I am studying your wonderful article on sumproduct. I have this:

A B
1 Make Month
2 Ford June
3 Ford June
4 Ford May
5 Ford June
6 Renault June
7 Renault June
8 BMW June
9 BMW May
10 BMW June

The question is, how many cars were either Fords or were sold in June?
I used
=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June"))
expecting to get 8. Yet, I get 11.
I was expecting + to work like logical or, but it worked like arithmetic
plus.
Did I miss something? Or, how can I fix this to get the expected answer?
James

Oops - I didn't read all of Bob's post. Never mind then.

"JMB" wrote:

One other way you could write it:
=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="June")0))


"Zone" wrote:

Bob, I am studying your wonderful article on sumproduct. I have this:

A B
1 Make Month
2 Ford June
3 Ford June
4 Ford May
5 Ford June
6 Renault June
7 Renault June
8 BMW June
9 BMW May
10 BMW June

The question is, how many cars were either Fords or were sold in June?
I used
=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June"))
expecting to get 8. Yet, I get 11.
I was expecting + to work like logical or, but it worked like arithmetic
plus.
Did I miss something? Or, how can I fix this to get the expected answer?
James

You didn't notice that Bob posted that same formula 5 hours ago?<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JMB" wrote in message
...
One other way you could write it:
=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="June")0))


"Zone" wrote:

Bob, I am studying your wonderful article on sumproduct. I have this:

A B
1 Make Month
2 Ford June
3 Ford June
4 Ford May
5 Ford June
6 Renault June
7 Renault June
8 BMW June
9 BMW May
10 BMW June

The question is, how many cars were either Fords or were sold in June?
I used
=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June"))
expecting to get 8. Yet, I get 11.
I was expecting + to work like logical or, but it worked like arithmetic
plus.
Did I miss something? Or, how can I fix this to get the expected
answer?
James

Nope - and I was wondering why he didn't because that's more than likely
where I got the idea. I read his post, but didn't scroll down enough to see
the rest of it (until I re-read it just to be sure).


"Ragdyer" wrote:

You didn't notice that Bob posted that same formula 5 hours ago?<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"JMB" wrote in message
...
One other way you could write it:
=SUMPRODUCT(--((A1:A10="Ford")+(B1:B10="June")0))


"Zone" wrote:

Bob, I am studying your wonderful article on sumproduct. I have this:

A B
1 Make Month
2 Ford June
3 Ford June
4 Ford May
5 Ford June
6 Renault June
7 Renault June
8 BMW June
9 BMW May
10 BMW June

The question is, how many cars were either Fords or were sold in June?
I used
=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June"))
expecting to get 8. Yet, I get 11.
I was expecting + to work like logical or, but it worked like arithmetic
plus.
Did I miss something? Or, how can I fix this to get the expected
answer?
James