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

Column A	Column B	Column C
Bob	Committed	3
Sarah	Proposed	2
Bob	Possible	10
Sarah	Committed	5

<tbody>
</tbody>

 

[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)