Hi all

I know this should be easy but I am having no luck so any help would be
great!!
I have two columns, column B for attendance (which is populated by an "x" if
attended) and column A for the number of hours attended. I want a total
number of hours attended, so am trying to say "if there is an x in column B,
then sum column a". I thought it would just be
if((B4-B195="x"),sum(a4:a195),"")
however it keeps returning a "value" error or sums the entire column
regardless of the attendance. Help!

Cheers
Soph

Hi

This Sumproduct formula should do it. Just remember to format the
resulting cell as time:

=SUMPRODUCT(--(B4:B195="X"),--(A4:A195))

Regards,
Per

On 10 Aug., 02:24, soph wrote:
Hi all

I know this should be easy but I am having no luck so any help would be
great!!
I have two columns, column B for attendance (which is populated by an "x" if
attended) and column A for the number of hours attended. *I want a total
number of hours attended, so am trying to say "if there is an x in column B,
then sum column a". *I thought it would just be
if((B4-B195="x"),sum(a4:a195),"")
*however it keeps returning a "value" error or sums the entire column
regardless of the attendance. *Help!

Cheers
Soph

=SUMIF(B4:B195,"x",A4:A195)
--
Jim Cone
Portland, Oregon USA



"soph"
wrote in message
Hi all
I know this should be easy but I am having no luck so any help would be
great!!
I have two columns, column B for attendance (which is populated by an "x" if
attended) and column A for the number of hours attended. I want a total
number of hours attended, so am trying to say "if there is an x in column B,
then sum column a". I thought it would just be
if((B4-B195="x"),sum(a4:a195),"")
however it keeps returning a "value" error or sums the entire column
regardless of the attendance. Help!

Cheers
Soph

perfect, thanks!! can I ask what the "--" is for?

"Per Jessen" wrote:

Hi

This Sumproduct formula should do it. Just remember to format the
resulting cell as time:

=SUMPRODUCT(--(B4:B195="X"),--(A4:A195))

Regards,
Per

On 10 Aug., 02:24, soph wrote:
Hi all

I know this should be easy but I am having no luck so any help would be
great!!
I have two columns, column B for attendance (which is populated by an "x" if
attended) and column A for the number of hours attended. I want a total
number of hours attended, so am trying to say "if there is an x in column B,
then sum column a". I thought it would just be
if((B4-B195="x"),sum(a4:a195),"")
however it keeps returning a "value" error or sums the entire column
regardless of the attendance. Help!

Cheers
Soph

Thanks for your reply.

-- is used to convert True/False values to 1/0 and it is actually not
needed in the last link of my formula.

=SUMPRODUCT(--(B4:B195="X"),B4:A195)

It can also be written like this:

=SUMPRODUCT((B4:B195="X")*A4:A195)

Hopes this helps.
....
Per


On 10 Aug., 02:43, soph wrote:
perfect, thanks!! *can I ask what the "--" is for?



"Per Jessen" wrote:
Hi

This Sumproduct formula should do it. Just remember to format the
resulting cell as time:

=SUMPRODUCT(--(B4:B195="X"),--(A4:A195))

Regards,
Per

On 10 Aug., 02:24, soph wrote:
Hi all

I know this should be easy but I am having no luck so any help would be
great!!
I have two columns, column B for attendance (which is populated by an "x" if
attended) and column A for the number of hours attended. *I want a total
number of hours attended, so am trying to say "if there is an x in column B,
then sum column a". *I thought it would just be
if((B4-B195="x"),sum(a4:a195),"")
*however it keeps returning a "value" error or sums the entire column
regardless of the attendance. *Help!

Cheers
Soph- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -