SUMPRODUCT - Bob Phillips

[Zone]

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 Guillett]

Don't 4 fords and 7 Junes=11 ?

 

[Bernard Liengme]

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 Liengme]

Or in shorter form
=SUMPRODUCT((A1:A10="Ford")+(B1:B10="June")-(A1:A10="Ford")*(B1:B10="June"))

 

[Zone]

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

 

[Zone]

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

 

[Bob Phillips]

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)

 

[Don Guillett]

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

 

[Zone]

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

 

[JMB]

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

 

[JMB]

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

 

[Ragdyer]

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

 

[JMB]

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

 

[Harlan Grove]

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]

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]

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.

 

[Bob Phillips]

"Bob Phillips" <[email protected]> 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(--D26)

 

[Harlan Grove]

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

It was several years ago, it started off with an argument between us about
the relative merits of *1 and +0. You mentioned using N(..) for clarity at
some point. I just can't find it in the Google Groups archive.

Yes you did, you said ...

Should have used


=SUMPRODUCT(--D26)

In what other thread? Because in my first response in this thread I wrote:

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

There are shorter array formulas.

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

I included a brief quote from what you wrote, but that's all. So which other
unrelated thread are you pulling this 'Should have used . . .' comment from
and attributing to me completely out of the context of this thread?