Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

share|improve this question

migrated from superuser.com Jul 5 '13 at 15:23

This question came from our site for computer enthusiasts and power users.

1 Answer 1

up vote 1 down vote accepted

A way of doing it is to make another column with

=IF(A2=1,C2,"")

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.

share|improve this answer
    
Yup, this worked great. Thanks! –  neu242 Jul 7 '13 at 19:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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