Sumproduct

[Lerner]

I used a formula given for SUMPRODUCT( given in this forum).
=SUMPRODUCT(--(A1:A2000="john
doe")*(B1:B2000="January")*(C1:C2000="Visit")*(D12000="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.

 

[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")*(D12000="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((D12000="newyork"),(D12000="washington"),(D12000="miami"),(D12000="Philadelphia")))

Hope this helps / Lars-Åke

 

[Bob Phillips]

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

 

[Lerner]

Nope.
I've been trying since your answer came and can not make it work.

 

[Lerner]

Obviously I AM DOING something wrong. It does not work either.

 

[Lerner]

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

 

[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

 

[Bob Phillips]

I'll tell you.

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

OR((D12000="newyork"),(D12000="washington"),(D12000="miami"),(D12000="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")*
((D12000="newyork")+(D12000="washington")+(D12000="miami")+(D12000="Philadelphia")))

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

 

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

 

[Lars-Åke Aspelin]

Thanks for the explanation on OR. I'll do my best to remember.

Lars-Åke