sum if

[soph]

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

 

[Per Jessen]

Hi

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

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

Regards,
Per

 

[Jim Cone]

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



"soph" <[email protected]>
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

 

[soph]

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

 

[Per Jessen]

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