Google Spreadsheets Range – How to Select a Range Programmatically in Google Spreadsheets

google sheetsworksheet-function

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.

Best Answer

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.

Related Topic