Question about sumproduct

[Wade Smart <wa...@gmail.com>]

I want to use sumproduct to tell me how many
games I have scheduled for a certain team.

=SUMPRODUCT(S152:S191="Green",V152:V191="2:00 PM")

Here is a typical line:
Nov 17, Green, vs, Lt Blue, 2:00 PM

The time when you click on it actually says 02:00:00 PM.

Ive tried different variations of how to write 2:00 but my result
is always "0".

Ive used this function when counting certain player types:
=SUMPRODUCT($AK$2:$AK$101="Dk Blue", $AE$2:$AE$101="Yes")
tells me how many goalies I have per team.

Is this a problem with time?
Wade


--
Registered Linux User: #480675
Registered Linux Machine: #408606
Linux since June 2005

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org

Re: Question about sumproduct

[Brian Barker <b....@btinternet.com>]

At 19:38 18/08/2013 -0500, Wade Smart wrote:
>I want to use sumproduct to tell me how many games I have scheduled 
>for a certain team.
>
>=SUMPRODUCT(S152:S191="Green",V152:V191="2:00 PM")
>
>Here is a typical line:
>Nov 17, Green, vs, Lt Blue, 2:00 PM
>
>The time when you click on it actually says 02:00:00 PM.

So what you have in column V is not text, then, but a proper time 
value, suitably formatted?

>I've tried different variations of how to write 2:00 but my result 
>is always "0".

As given in your formula, you are trying to match a text string - 
"2:00 PM" - with numerical time values, which happen to be formatted 
to look the same.

>Is this a problem with time?

Yes - in that you need to compare like with like.  Probably the 
simplest way is to express the time in your SUMPRODUCT() function as 
TIMEVALUE("2:00 PM").  Alternatively  you could use TIME(14;0;0).

I trust this helps.

Brian Barker


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org