Forum Discussion

DarkTalent's avatar
DarkTalent
Copper Contributor
Mar 28, 2023

Using a dropdown to determine range used by a countif formula

G'day

 

Within a sheet I have a bunch of columns that contain dates for when particular activities have occurred for the relevant row record. Not every cell in the columns are populated, only if that activity has happened.

 

Eg

Record -- Activity A --- Activity B --- Activity C

1 -------- 01/01/23 --- <blank> ---- 03/01/23

2 -------- <blank> ---- 01/01/23 --- 01/01/03

 

Etc

 

I'm trying to write a formula in a separate workbook to count the number of occurrences in a column, as determined by a pair of cells with drop down lists - D4 and D5.

D4 has months and D5 has the activity (10 in total).

 

I want to be able to choose an activity in D5 and nominate a month in D4, then have a third cell provide the total times that activity occurred in the specified month. So in the above example, if d5 = Activity A, then the third cell's formula (a countif I'm assuming) needs to refer to column 2, but if Activity C is selected, the third cell formula refers to column 4.

 

I have managed to use a match and index formula to find the column number. I'm just stumped how to use this to determine the countif range. 

 

Any tips? 

excel
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Mar 28, 2023

    DarkTalent 

    You can use the COUNTIF function to count the number of occurrences in a column based on a pair of cells with drop-down lists. Here’s how you can do it:

    1. Use the INDEX and MATCH functions to find the column number that corresponds to the activity selected in D5. For example, if D5 = Activity A, then use INDEX and MATCH to find the column number for Activity A.
    2. Use the COUNTIF function to count the number of occurrences of the activity in the column found in step 1 that corresponds to the month selected in D4.

    Here’s an example formula that you can use:

    =COUNTIF(INDEX(A:C,0,MATCH(D5,A1:C1,0)),D4)

    This formula assumes that your data is in columns A through C and that row 1 contains your activity names. You can change A:C and A1:C1 to match your data.

     

    I hope this helps! … ...and I understood it correctly 😊

Resources