I have a spreadsheet like this:

active  date        name
1       1-Jun-2013  Paul
1       4-Jun-2013  Sheela
1       8-Jun-2013  Paul
1       2-Jul-2013  Henrik
        3-Jul-2013  Henrik
        10-Jul-2013 Paul
        14-Jul-2013 Sheela

I want a counter for {how many times a name is mentioned AND active == 1}. In practice, the active = 1 is manually set when the date has passed, so the criteria could instead be {how many times a name is mentioned AND date < today}.

The resulting list should be like this:

Henrik  1
Paul    2
Sheela  1

I can use =IFCOUNT(C2:C8, "Henrik") to count all occurances of "Henrik", but that doesn't take the active (or date) column into account.

So, If I could programatically select C2:C5 (since A5 is the last date with active == 1, or the B2:B5 cells have a date < today) or make the IFCOUNT check for active == 1 in addition to cellcontent == "Henrik", that would solve this.

1 Answer 1


A way of doing it is to make another column with


A2 being the active flag and then do the `COUNTIF` on that column.

You could also replace the A2=1 with a date test if you wanted.

  • Yup, this worked great. Thanks!
    – neu242
    Jul 7, 2013 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.