Sumproduct

L

Lerner

I used a formula given for SUMPRODUCT( given in this forum).
=SUMPRODUCT(--(A1:A2000="john
doe")*(B1:B2000="January")*(C1:C2000="Visit")*(D1:D2000="newyork"))
The column D could be Newyork, Washington, Miami or Philaderphia and I'like
to have the sumproduct with the same criteria for column A,B,C but D could
change between those states without making any difference in my counting.
The question is How can I add those states to the original formula in order
to include them in the sumproduct as if it were Newyork.
Something like "and"," or"...
Tx.
 
L

Lars-Åke Aspelin

I used a formula given for SUMPRODUCT( given in this forum).
=SUMPRODUCT(--(A1:A2000="john
doe")*(B1:B2000="January")*(C1:C2000="Visit")*(D1:D2000="newyork"))
The column D could be Newyork, Washington, Miami or Philaderphia and I'like
to have the sumproduct with the same criteria for column A,B,C but D could
change between those states without making any difference in my counting.
The question is How can I add those states to the original formula in order
to include them in the sumproduct as if it were Newyork.
Something like "and"," or"...
Tx.


Try this formula (all on one line):

=SUMPRODUCT((A1:A2000="john
doe")*(B1:B2000="January")*(C1:C2000="Visit")*OR((D1:D2000="newyork"),(D1:D2000="washington"),(D1:D2000="miami"),(D1:D2000="Philadelphia")))

Hope this helps / Lars-Åke
 
B

Bob Phillips

=SUMPRODUCT(--(A1:A2000="john doe")*(B1:B2000="January")*
(C1:C2000="Visit")*(D1:D2000={"newyork","washington","miami","philadelphia"}))
 
L

Lerner

Yes I was doing something wrong.
Thank you Bob.
I'll try yours later again Lars, when my mind gets clear.
Thank you.
 
L

Lars-Åke Aspelin

"I does not work" is not a helpful description of your problem.

Please give an example of input in A1, B1, C1, and D,
what your expected output is, and what you get by applying the
proposed formula.

Lars-Åke
 
B

Bob Phillips

I'll tell you.

That is not how you do ORs in array functions, of which SP is one. This bit

OR((D1:D2000="newyork"),(D1:D2000="washington"),(D1:D2000="miami"),(D1:D2000="Philadelphia"))

will return TRUE if ANY value in that range is one of those values, in which
case every other instance of the other 3 conditions will get counted, even
if the town does not match. The proper way to do ORs is

=SUMPRODUCT((A1:A2000="john doe")*(B1:B2000="January")*(C1:C2000="Visit")*
((D1:D2000="newyork")+(D1:D2000="washington")+(D1:D2000="miami")+(D1:D2000="Philadelphia")))

and I am sure that you have suggested that solution previously and I pointed
it out to you then.
 
L

Lerner

No one could explain it better, thanks Bob for taking the words out of my mouth
(just kidding). Thanks to you too Lars for your time, I appreciate it.
PS: Bob's formula also give me the opportunity to add more items without
adding the range everytime. Let's take it as a learning experience.
Thanks again, guys.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top