Sum up cells based on the value yes/no

[Karlbirger]

Hi guys,

I have two columns, A and B. In column A the value can be either "Yes" or "No".
I have problems to engineer a formula that sum up all cells in column B where the equivalent cells in column A equals "Yes".
Is this possible to construct in Worksheet_SelectionChange and add to a specific cell so I dont have to update it manually to see the total value?

Thanks for the help.

Regards
KB

 

[VoG]

Try

=SUMIF(A:A,"Yes",B:B)

 

[Karlbirger]

**** that was an easy code.

Big thumbs up VoG!

Thanks
KB

 

[Karlbirger]

If I would like to add an extra cell in column C, e.g the value 2011 (the year of today), can I add this into the code

=SUMIF(H:H;"Yes";AI:AI)

in any way?

In other words: If the cell in column A says "Yes" and the cell in column C says "2011", then sum up the equivalent cells in column B.

Thanks in advance
KB

 

[VoG]

Try

=SUMPRODUCT(--(A1:A1000="Yes"),--(C1:C1000=2011),B1:B1000)

 

[Karlbirger]

Cant really get that code to work. I wrote:

=SUMPRODUCT(;(A1:A1000="Yes");(C1:C1000=2011);B1:B1000)

I have tried this one too but it gives me an error:

=SUMIFS(B:B;A:A;"Yes";C:C;"2011")

Shouldnt SUMIFS be appropriate here?

 

[VoG]

Try

=SUMPRODUCT(--(A1:A1000="Yes");--(C1:C1000=2011);B1:B1000)

 

[Karlbirger]

Works, but it returns "0", which it shouldn't. There are values in column B that have equivalent values of "Yes" and "2011".

Any ideas?

 

[VoG]

In column C are the values actually 2011 or are they dates formatted to show only the year?

 

[Karlbirger]

My mistake, you are correct. The cells are formated to show only the year of a full date.