Excel countif multiple criteria overlapping ranges

问题:

I've got a membership database and am trying to figure out what the dropoff is for a rolling 3 month period. My current thought is that I need to count if the first cell is not blank (meaning the person wasn't yet a member) and if either of the other two cells is blank (meaning that they dropped off). In the sample data below I'm just using a 1 if the person is a member in the month

My latest attempt is:

=COUNTIF(B2:B5,"<>"&"*",[C2:C5,"<>"&"*",D2:D5,"<>"&"*"])

Toy data:

    A       B        C       D
1   Mem#   May-16  June-16  July-16
2   001     1       1        1
3   002             1        1
4   003     1       1
5   004     1       1

Desired output would be "2", the number of members that had membership at the start of the period but not at the end of it.

回答1:

Use COUNTIFS():

=COUNTIFS(B:B,"<>",D:D,"=")