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.

On Sun, 8 Feb 2009 13:04:01 -0800, Lerner
wrote:

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:D2 000="miami"),(D1:D2000="Philadelphia")))

Hope this helps / Lars-Åke

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


--
__________________________________
HTH

Bob

"Lerner" wrote in message
...
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.

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


"Lars-Ã…ke Aspelin" wrote:

On Sun, 8 Feb 2009 13:04:01 -0800, Lerner
wrote:

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:D2 000="miami"),(D1:D2000="Philadelphia")))

Hope this helps / Lars-Ã…ke

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

"Bob Phillips" wrote:

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


--
__________________________________
HTH

Bob

"Lerner" wrote in message
...
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.

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

"Lerner" wrote:

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

"Bob Phillips" wrote:

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


--
__________________________________
HTH

Bob

"Lerner" wrote in message
...
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.

"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


On Sun, 8 Feb 2009 14:21:01 -0800, Lerner
wrote:

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

"Bob Phillips" wrote:

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


--
__________________________________
HTH

Bob

"Lerner" wrote in message
...
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.

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"),(D 1: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.


--
__________________________________
HTH

Bob

"Lars-Åke Aspelin" wrote in message
...
"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


On Sun, 8 Feb 2009 14:21:01 -0800, Lerner
wrote:

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

"Bob Phillips" wrote:

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


--
__________________________________
HTH

Bob

"Lerner" wrote in message
...
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.

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.

"Bob Phillips" wrote:

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"),(D 1: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.


--
__________________________________
HTH

Bob

"Lars-Ã…ke Aspelin" wrote in message
...
"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


On Sun, 8 Feb 2009 14:21:01 -0800, Lerner
wrote:

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

"Bob Phillips" wrote:

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


--
__________________________________
HTH

Bob

"Lerner" wrote in message
...
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.

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

Lars-Åke

On Sun, 8 Feb 2009 23:59:19 -0000, "Bob Phillips"
wrote:

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.