SumIF column a = X AND column b = Y OR Z

[adgroves]

I'd like to calculate the number of days (column c) that each staff member has

1) committed
2) committed + proposed
3) committed + proposed + possible

I need a formula to the effect of: SumIF column a = X AND column b = Y OR Z



[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C [/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Committed[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]Proposed[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Bob [/TD]
[TD]Possible[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]Committed[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

Use SUMPRODUCT
=SUMPRODUCT((A1:A3=X)*((B1:B3=Y)+(B1:B3=Z)))

 

[Aladin Akyurek]

Like this:

=SUMIFS(C:C,A:A,"bob",B:B,"committed")

=SUM(SUMIFS(C:C,A:A,"bob",B:B,{"committed","proposed"}))

=SUM(SUMIFS(C:C,A:A,"bob",B:B,{"committed","proposed","possible"}))

The last one can be re-written if there are 3 choices only in B...

=SUMIFS(C:C,A:A,"bob",B:B,"?*")

 

[JeanKim]

1) =SUMPRODUCT(--(A1:A4="Bob"),--(B1:B4="Committed"),C1:C4)
2) =SUMPRODUCT(--(A1:A4="Bob"),--(B1:B4="Committed"),C1:C4) + SUMPRODUCT(--(A1:A4="Bob"),--(B1:B4="proposed"),C1:C4)
3) =SUMPRODUCT(--(A1:A4="Bob"),--(B1:B4="Committed"),C1:C4) + SUMPRODUCT(--(A1:A4="Bob"),--(B1:B4="proposed"),C1:C4) + =SUMPRODUCT(--(A1:A4="Bob"),--(B1:B4="possible"),C1:C4)