I am trying to make a formula that gives me a count of how many row
have the same date and time...i have tried everything i can think o
and can't seem to get it...any help is appreciated!

i.e.
saturday 14:00
saturday 11:00
saturday 14:00
saturday 14:00
saturday 14:00
monday 14:00
monday 14:00
tuesday 4:00
tuesday 14:00
tuesday 1:00

there will be 7 fields...one for each day and time...so in the abov
example they should displayi left a lot of fields out below...onl
mentioned the ones that are used in the example)
saturday/14:00 - 4
saturday/11:00 - 1
monday/14:00 - 2
tuesday/4:00 - 1
tuesday/14:00 - 1
tuesday/1:00 -

--
cree
-----------------------------------------------------------------------
creed's Profile: http://www.excelforum.com/member.php...fo&userid=3442
View this thread: http://www.excelforum.com/showthread.php?threadid=54192

try this idea
=sumproduct((a2:a22="saturday")*(b2:b22=14:00))

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"creed" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>
> I am trying to make a formula that gives me a count of how many rows
> have the same date and time...i have tried everything i can think of
> and can't seem to get it...any help is appreciated!
>
> i.e.
> saturday 14:00
> saturday 11:00
> saturday 14:00
> saturday 14:00
> saturday 14:00
> monday 14:00
> monday 14:00
> tuesday 4:00
> tuesday 14:00
> tuesday 1:00
>
> there will be 7 fields...one for each day and time...so in the above
> example they should displayi left a lot of fields out below...only
> mentioned the ones that are used in the example)
> saturday/14:00 - 4
> saturday/11:00 - 1
> monday/14:00 - 2
> tuesday/4:00 - 1
> tuesday/14:00 - 1
> tuesday/1:00 - 1
>
>
> --
> creed
> ------------------------------------------------------------------------
> creed's Profile:
> http://www.excelforum.com/member.php...o&userid=34424
> View this thread: http://www.excelforum.com/showthread...hreadid=541926
>

i used =SUMPRODUCT((A2:A50000="Saturday")*(E2:E50000="8:00"))

and it gave me a 0 instead of the 9 it should be


--
creed
------------------------------------------------------------------------
creed's Profile: http://www.excelforum.com/member.php...o&userid=34424
View this thread: http://www.excelforum.com/showthread...hreadid=541926

Try

=SUMPRODUCT((A2:A50000="Saturday")*(E2:E50000=--"08:00:00"))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"creed" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>
> i used =SUMPRODUCT((A2:A50000="Saturday")*(E2:E50000="8:00"))
>
> and it gave me a 0 instead of the 9 it should be
>
>
> --
> creed
> ------------------------------------------------------------------------
> creed's Profile:
http://www.excelforum.com/member.php...o&userid=34424
> View this thread: http://www.excelforum.com/showthread...hreadid=541926
>

try it this way
=SUMPRODUCT(($C$1:$C$5="Saturday")*(TEXT($D$1:$D$5,"hh:mm")="14:00"))

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"creed" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
>
> i used =SUMPRODUCT((A2:A50000="Saturday")*(E2:E50000="8:00"))
>
> and it gave me a 0 instead of the 9 it should be
>
>
> --
> creed
> ------------------------------------------------------------------------
> creed's Profile:
> http://www.excelforum.com/member.php...o&userid=34424
> View this thread: http://www.excelforum.com/showthread...hreadid=541926
>

or where g1 contains saturday & g2 contains 14:00
=SUMPRODUCT(($C$1:$C$5=g1)*($D$1:$D$5=G2))

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> try it this way
> =SUMPRODUCT(($C$1:$C$5="Saturday")*(TEXT($D$1:$D$5,"hh:mm")="14:00"))
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "creed" <(E-Mail Removed)> wrote in
> message news:(E-Mail Removed)...
>>
>> i used =SUMPRODUCT((A2:A50000="Saturday")*(E2:E50000="8:00"))
>>
>> and it gave me a 0 instead of the 9 it should be
>>
>>
>> --
>> creed
>> ------------------------------------------------------------------------
>> creed's Profile:
>> http://www.excelforum.com/member.php...o&userid=34424
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=541926
>>
>
>

that gave me a 1

could it be messing up the numbers because i'm using a forula to get
those times?

the time field isn't directly input, i'm using a forumla to round the
actual time to the nearest hour


--
creed
------------------------------------------------------------------------
creed's Profile: http://www.excelforum.com/member.php...o&userid=34424
View this thread: http://www.excelforum.com/showthread...hreadid=541926

oops...i lied...it didn't actually change it to a 1, it stayed 0...
forgot i switched sum to count to check i

--
cree
-----------------------------------------------------------------------
creed's Profile: http://www.excelforum.com/member.php...fo&userid=3442
View this thread: http://www.excelforum.com/showthread.php?threadid=54192

ok, i got it...the second thing you mentioned worked...i forgot to ad
sumproduct when i changed back from count...i just wrote sum..

thanks for the help

--
cree
-----------------------------------------------------------------------
creed's Profile: http://www.excelforum.com/member.php...fo&userid=3442
View this thread: http://www.excelforum.com/showthread.php?threadid=54192