Forum Discussion
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 th...
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:
- 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.
- 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 😊
Thanks NikolinoDE - I'll have a crack and let you know how I went. Much appreciated.