openoffice-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brian Barker <b.m.bar...@btinternet.com>
Subject Re: Question about sumproduct
Date Mon, 19 Aug 2013 01:01:29 GMT
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


Mime
View raw message