=SUM((C2:C7000="Friday")*(F2:F7000="8:00:00 AM")) not adding
 

HI there,

I am so close but this will not add any values.
I do ctrl shift enter and it accepts the formula but I only get a 0.
Any ideas? Time cell is formatted as time and Friday is formatted as
General.

=SUM((C2:C7000="Friday")*(F2:F7000="8:00:00 AM")) not adding
 

My approach to this is normally to restrict the range (initially) to
say three or four rows where you know you should get a result, then
examine the actual evaluations of the formula. For example, what
does:

(C100:C104="Friday")

evaluate to? Is it perhaps (FALSE, FALSE, FALSE, FALSE) when you
expected (FALSE, TRUE, FALSE, FALSE)?

If so, then you can focus on working out why, say, C101 < "Friday"
when you think it should.

HTH,

--

Alan.

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:



This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb




"chubbybat" wrote in message
...
HI there,

I am so close but this will not add any values.
I do ctrl shift enter and it accepts the formula but I only get a 0.
Any ideas? Time cell is formatted as time and Friday is formatted as
General.

=SUM((C2:C7000="Friday")*(F2:F7000="8:00:00 AM")) not adding
 

On Mar 5, 3:50*pm, "Alan" wrote:
My approach to this is normally to restrict the range (initially) to
say three or four rows where you know you should get a result, then
examine the actual evaluations of the formula. *For example, what
does:

(C100:C104="Friday")

evaluate to? *Is it perhaps (FALSE, FALSE, FALSE, FALSE) when you
expected (FALSE, TRUE, FALSE, FALSE)?

If so, then you can focus on working out why, say, C101 < "Friday"
when you think it should.

HTH,

--

Alan.

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:



This is valid as is. *It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. *If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb

"chubbybat" wrote in message

...



HI there,

I am so close but this will not add any values.
I do ctrl shift enter and it accepts the formula but I only get a 0.
Any ideas? Time cell is formatted as time and Friday is formatted as
General.- Hide quoted text -

- Show quoted text -

Friday 8:00:00
Friday 8:00:00
Friday 8:00:00
Friday 8:00:00
Friday 8:00:00
Friday 8:00:00
Friday 8:00:00
Friday 8:00:00
Friday 8:00:00
Friday 8:00:00
Friday 8:00:00
Friday 8:00:00

where Friday is C2-C15 (or so) and 8:00:00 is D2-D15(or so)
my formula is not adding them up.
=SUM((C2:C7000="Friday")*(D2:D7000="8:00:00"))

=SUM((C2:C7000="Friday")*(F2:F7000="8:00:00 AM")) not adding
 

is the data in column C actually the text string "Friday" or is it a date (ie
- a numeric value) that is custom formatted dddd to only display "Friday"?

Same for the time in column D. It is probably not a text string "8:00:00",
but rather a numeric date/time value. You might try

(D2:D7000=TIMEVALUE("8:00:00"))


"chubbybat" wrote:

On Mar 5, 3:50 pm, "Alan" wrote:
My approach to this is normally to restrict the range (initially) to
say three or four rows where you know you should get a result, then
examine the actual evaluations of the formula. For example, what
does:

(C100:C104="Friday")

evaluate to? Is it perhaps (FALSE, FALSE, FALSE, FALSE) when you
expected (FALSE, TRUE, FALSE, FALSE)?

If so, then you can focus on working out why, say, C101 < "Friday"
when you think it should.

HTH,

--

Alan.

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:



This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb

"chubbybat" wrote in message

...



HI there,

I am so close but this will not add any values.
I do ctrl shift enter and it accepts the formula but I only get a 0.
Any ideas? Time cell is formatted as time and Friday is formatted as
General.- Hide quoted text -

- Show quoted text -

Friday 8:00:00
Friday 8:00:00
Friday 8:00:00
Friday 8:00:00
Friday 8:00:00
Friday 8:00:00
Friday 8:00:00
Friday 8:00:00
Friday 8:00:00
Friday 8:00:00
Friday 8:00:00
Friday 8:00:00

where Friday is C2-C15 (or so) and 8:00:00 is D2-D15(or so)
my formula is not adding them up.
=SUM((C2:C7000="Friday")*(D2:D7000="8:00:00"))

=SUM((C2:C7000="Friday")*(F2:F7000="8:00:00 AM")) not adding
 

Try the array formula:

=SUM((C2:C7000="Friday")*(F2:F7000=TIME(8,0,0)))

Or, this non-array version:

=SUMPRODUCT(--(C2:C7000="Friday"),--(F2:F7000=TIME(8,0,0)))

--
Biff
Microsoft Excel MVP


"chubbybat" wrote in message
...
HI there,

I am so close but this will not add any values.
I do ctrl shift enter and it accepts the formula but I only get a 0.
Any ideas? Time cell is formatted as time and Friday is formatted as
General.

=SUM((C2:C7000="Friday")*(F2:F7000="8:00:00 AM")) not adding
 

On Mar 5, 4:23 pm, "T. Valko" wrote:
Try the array formula:

=SUM((C2:C7000="Friday")*(F2:F7000=TIME(8,0,0)))

Or, this non-array version:

=SUMPRODUCT(--(C2:C7000="Friday"),--(F2:F7000=TIME(8,0,0)))

--
Biff
Microsoft Excel MVP

"chubbybat" wrote in message

...

HI there,

I am so close but this will not add any values.
I do ctrl shift enter and it accepts the formula but I only get a 0.
Any ideas? Time cell is formatted as time and Friday is formatted as
General.

Thank you everyone, I will give these methods a try.

=SUM((C2:C7000="Friday")*(F2:F7000="8:00:00 AM")) not adding
 

Hi,

If you do the test I suggested above (a couple or so rows will be
sufficient) what results do you get within the inner calcs?

Actually evaluate just the logical tests to get something like:

=SUM(({FALSE;TRUE;FALSE;FALSE})*({FALSE;TRUE;FALSE ;FALSE}))

Pick any one that is wrong, then do something like:

=C101="Friday"

Presumably that evaluates to FALSE (based on the test being wrong).

Now evaluate C101 (or whatever cell you used) and see what it actually
contains.

HTH,

--

Alan.

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:



This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb

=SUM((C2:C7000="Friday")*(F2:F7000="8:00:00 AM")) not adding
 

On Mar 5, 5:02 pm, chubbybat wrote:
On Mar 5, 4:23 pm, "T. Valko" wrote:



Try the array formula:

=SUM((C2:C7000="Friday")*(F2:F7000=TIME(8,0,0)))

Or, this non-array version:

=SUMPRODUCT(--(C2:C7000="Friday"),--(F2:F7000=TIME(8,0,0)))

--
Biff
Microsoft Excel MVP

"chubbybat" wrote in message

...

HI there,

I am so close but this will not add any values.
I do ctrl shift enter and it accepts the formula but I only get a 0.
Any ideas? Time cell is formatted as time and Friday is formatted as
General.

Thank you everyone, I will give these methods a try.

Hi,

thank you all for the help, the end result was ...
=SUM((B2:B16="Friday")*(C2:C716=TIMEVALUE("8:00:00 "))) and it worked a
treat.
Keep on Geeking it up guys!

=SUM((C2:C7000="Friday")*(F2:F7000="8:00:00 AM")) not adding
 

"chubbybat" wrote in message
...
On Mar 5, 5:02 pm, chubbybat wrote:
On Mar 5, 4:23 pm, "T. Valko" wrote:



Try the array formula:

=SUM((C2:C7000="Friday")*(F2:F7000=TIME(8,0,0)))

Or, this non-array version:

=SUMPRODUCT(--(C2:C7000="Friday"),--(F2:F7000=TIME(8,0,0)))

--
Biff
Microsoft Excel MVP

"chubbybat" wrote in message

...

HI there,

I am so close but this will not add any values.
I do ctrl shift enter and it accepts the formula but I only get a 0.
Any ideas? Time cell is formatted as time and Friday is formatted as
General.

Thank you everyone, I will give these methods a try.

Hi,

thank you all for the help, the end result was ...
=SUM((B2:B16="Friday")*(C2:C716=TIMEVALUE("8:00:00 "))) and it worked a
treat.
Keep on Geeking it up guys!

Glad you got it working.

These expressions do exactly the same thing:

TIMEVALUE("8:00:00")
TIME(8,0,0)

--
Biff
Microsoft Excel MVP

=SUM((C2:C7000="Friday")*(F2:F7000="8:00:00 AM")) not adding
 

In your formula

=SUM((C2:C7000="Friday")*(F2:F7000="8:00:00 AM"))



"8:00:00 AM" is text, not time. Try


=SUM((C2:C7000="Friday")*(F2:F7000=TIMEVALUE("8:00 :00 AM")))


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"chubbybat" wrote in message
...
HI there,

I am so close but this will not add any values.
I do ctrl shift enter and it accepts the formula but I only get a 0.
Any ideas? Time cell is formatted as time and Friday is formatted as
General.