sum if
 

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

sum if
 

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

sum if
 

=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

sum if
 

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

sum if
 

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 -