I want to add all values in column F, (a+b+c etc, below) corresponding to a
description in column C of "08:15" and to ensure that any value corresponding
to ANY TIME OTHER THAN 08:15 was NOT INCLUDED in the total

Example:

C1 contains the time "08:15"
F1 contains the value to be added, which I will call "a"
C365 contains the time "08:15"
F365 contains the second value to be added which I will call "b"
C729 contains the time "08:15"
F729 contains the third value to be added which I will call "c" , etc etc

Two questions, then:

1. What formula could I input to achieve the above, i.e. to get the sum of
a+b+c ?

2. I also want to ensure that if one of the cells in column C showed a time
other than 08:15 that the value in the corresponding column F was NOT added,
so for example if C729 above had "08:30" instead of "08:15", I would not want
value "c" above included in my total.

Many thanks for your help !!!!

Try

=SUMPRODUCT(--(C1:C1000="08:15"),F1:F1000)


"Zakynthos" wrote:

I want to add all values in column F, (a+b+c etc, below) corresponding to a
description in column C of "08:15" and to ensure that any value corresponding
to ANY TIME OTHER THAN 08:15 was NOT INCLUDED in the total

Example:

C1 contains the time "08:15"
F1 contains the value to be added, which I will call "a"
C365 contains the time "08:15"
F365 contains the second value to be added which I will call "b"
C729 contains the time "08:15"
F729 contains the third value to be added which I will call "c" , etc etc

Two questions, then:

1. What formula could I input to achieve the above, i.e. to get the sum of
a+b+c ?

2. I also want to ensure that if one of the cells in column C showed a time
other than 08:15 that the value in the corresponding column F was NOT added,
so for example if C729 above had "08:30" instead of "08:15", I would not want
value "c" above included in my total.

Many thanks for your help !!!!

I gave it a go, inputting the formula:

=SUMPRODUCT(--(C1:C1000="08:15"),F1:F1000)

into, say, G1, G53 etc, both cells corresponding with 8:15 am for 2
consecutive days - where F1 and F53 contained 2 of the values I wanted to
add - and the formula certainly LOOKS as if it SHOULD produce the correct
result - BUT... all I got was a: #DIV/0!

Any suggestions about where I've gone (horribly) wrong???





"Duke Carey" wrote:

Try

=SUMPRODUCT(--(C1:C1000="08:15"),F1:F1000)


"Zakynthos" wrote:

I want to add all values in column F, (a+b+c etc, below) corresponding to a
description in column C of "08:15" and to ensure that any value corresponding
to ANY TIME OTHER THAN 08:15 was NOT INCLUDED in the total

Example:

C1 contains the time "08:15"
F1 contains the value to be added, which I will call "a"
C365 contains the time "08:15"
F365 contains the second value to be added which I will call "b"
C729 contains the time "08:15"
F729 contains the third value to be added which I will call "c" , etc etc

Two questions, then:

1. What formula could I input to achieve the above, i.e. to get the sum of
a+b+c ?

2. I also want to ensure that if one of the cells in column C showed a time
other than 08:15 that the value in the corresponding column F was NOT added,
so for example if C729 above had "08:30" instead of "08:15", I would not want
value "c" above included in my total.

Many thanks for your help !!!!

1) I'm very confused about how you could have gotten a divide by zero error.
2) You need to use the formula in only one cell, as it will sum all of the
column F values where the text in C = "08:15". Simply copy the original
formula from the post and paste it into a single cell

Now, if column C actually contains time values and not text representations
of time, that's a different story. If that's the case, post back and we'll
give you a modified formula.

"Zakynthos" wrote:

I gave it a go, inputting the formula:

=SUMPRODUCT(--(C1:C1000="08:15"),F1:F1000)

into, say, G1, G53 etc, both cells corresponding with 8:15 am for 2
consecutive days - where F1 and F53 contained 2 of the values I wanted to
add - and the formula certainly LOOKS as if it SHOULD produce the correct
result - BUT... all I got was a: #DIV/0!

Any suggestions about where I've gone (horribly) wrong???





"Duke Carey" wrote:

Try

=SUMPRODUCT(--(C1:C1000="08:15"),F1:F1000)


"Zakynthos" wrote:

I want to add all values in column F, (a+b+c etc, below) corresponding to a
description in column C of "08:15" and to ensure that any value corresponding
to ANY TIME OTHER THAN 08:15 was NOT INCLUDED in the total

Example:

C1 contains the time "08:15"
F1 contains the value to be added, which I will call "a"
C365 contains the time "08:15"
F365 contains the second value to be added which I will call "b"
C729 contains the time "08:15"
F729 contains the third value to be added which I will call "c" , etc etc

Two questions, then:

1. What formula could I input to achieve the above, i.e. to get the sum of
a+b+c ?

2. I also want to ensure that if one of the cells in column C showed a time
other than 08:15 that the value in the corresponding column F was NOT added,
so for example if C729 above had "08:30" instead of "08:15", I would not want
value "c" above included in my total.

Many thanks for your help !!!!

Sorry!

My fault for not checking through all the columns carefully enough. Column C
does contain time values, but the problem leading to the divide by zero error
was that some cells in column F were blank because there was no data for
Sundays in the month. I've corrected this by inserting random data. Many
thanks for your help.

"Duke Carey" wrote:

1) I'm very confused about how you could have gotten a divide by zero error.
2) You need to use the formula in only one cell, as it will sum all of the
column F values where the text in C = "08:15". Simply copy the original
formula from the post and paste it into a single cell

Now, if column C actually contains time values and not text representations
of time, that's a different story. If that's the case, post back and we'll
give you a modified formula.

"Zakynthos" wrote:

I gave it a go, inputting the formula:

=SUMPRODUCT(--(C1:C1000="08:15"),F1:F1000)

into, say, G1, G53 etc, both cells corresponding with 8:15 am for 2
consecutive days - where F1 and F53 contained 2 of the values I wanted to
add - and the formula certainly LOOKS as if it SHOULD produce the correct
result - BUT... all I got was a: #DIV/0!

Any suggestions about where I've gone (horribly) wrong???





"Duke Carey" wrote:

Try

=SUMPRODUCT(--(C1:C1000="08:15"),F1:F1000)


"Zakynthos" wrote:

I want to add all values in column F, (a+b+c etc, below) corresponding to a
description in column C of "08:15" and to ensure that any value corresponding
to ANY TIME OTHER THAN 08:15 was NOT INCLUDED in the total

Example:

C1 contains the time "08:15"
F1 contains the value to be added, which I will call "a"
C365 contains the time "08:15"
F365 contains the second value to be added which I will call "b"
C729 contains the time "08:15"
F729 contains the third value to be added which I will call "c" , etc etc

Two questions, then:

1. What formula could I input to achieve the above, i.e. to get the sum of
a+b+c ?

2. I also want to ensure that if one of the cells in column C showed a time
other than 08:15 that the value in the corresponding column F was NOT added,
so for example if C729 above had "08:30" instead of "08:15", I would not want
value "c" above included in my total.

Many thanks for your help !!!!