Using IF Statements to calculate availability

[ireid3176]

Hi All,

I'm looking to make a simple calculator to help determine when holidays are booked what skills we will lose as a consequence. Simple table below. In my simple thinking, i thought for totals an IF statement would be something as follows:

If (B2=1, COUNT C2),If (B3=1, COUNT C3),If (B4=1, COUNT C4),If (B5=1, COUNT C5)

A B C D E
Name Working? Skill1 Skill2 Skill3
Bob 1 1 0 0
Terry 0 1 1 1
Jim 1 1 0 1
Lee 0 0 0 1

Totals 2 0 1

My formula give me an error and i know i'm using incorrect syntax. Can anyone suggest an alternative?

Regards

 

[Kamolga]

[TABLE="width: 384"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Name[/TD]
[TD="width: 64, bgcolor: transparent"]Working[/TD]
[TD="width: 64, bgcolor: transparent"]Skill 1[/TD]
[TD="width: 64, bgcolor: transparent"]Skill 2[/TD]
[TD="width: 64, bgcolor: transparent"]Skill 3[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Bon[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Terry[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Jim[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lee[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Total[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]


The 2 is in C7 and formula is

=SUMIF($B2:$B5,1,C2:C5)

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent, align: right"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
You can drag it for skill 2 & 3

 

[AlphaFrog]

=sumif($b$2:$b$5,1,c2:c5)
=sumif($b$2:$b$5,1,d2:d5)
=sumif($b$2:$b$5,1,e2:e5)