using AND in a countif formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

creed - 14 May 2006 13:15 GMT

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 display:(i 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 -

Reply to this Message

Don Guillett - 14 May 2006 13:25 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>
> I am trying to make a formula that gives me a count of how many rows
[quoted text clipped - 22 lines]
> tuesday/14:00 - 1
> tuesday/1:00 - 1

Reply to this Message

creed - 14 May 2006 13:44 GMT

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

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

Signature

creed

Reply to this Message

Bob Phillips - 14 May 2006 13:58 GMT

Try

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

Signature

HTH

Bob Phillips

(remove xxx from email address if mailing direct)

> i used =SUMPRODUCT((A2:A50000="Saturday")*(E2:E50000="8:00"))
>
> and it gave me a 0 instead of the 9 it should be

Reply to this Message

Don Guillett - 14 May 2006 14:07 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>
> i used =SUMPRODUCT((A2:A50000="Saturday")*(E2:E50000="8:00"))
>
> and it gave me a 0 instead of the 9 it should be

Reply to this Message

Don Guillett - 14 May 2006 14:13 GMT

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

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

> try it this way
> =SUMPRODUCT(($C$1:$C$5="Saturday")*(TEXT($D$1:$D$5,"hh:mm")="14:00"))
>
>> i used =SUMPRODUCT((A2:A50000="Saturday")*(E2:E50000="8:00"))
>>
>> and it gave me a 0 instead of the 9 it should be

Reply to this Message

creed - 14 May 2006 14:18 GMT

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

Signature

creed

Reply to this Message

creed - 14 May 2006 14:20 GMT

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

Reply to this Message

creed - 14 May 2006 14:22 GMT

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

Reply to this Message