0

I have a file that contains a planner with dates in the lines and the roles of my team members in the columns.

I need to be able to count how many people are working per day and per role.

enter image description here I need to get the sum per Role and for a specific date, with the date being a variable so I just create a cell with the date and change it there to easily filter for the different days. Basically, for the 13th, I would get "2" for Role 1 and 0 for Role 2.

Then, if I want to get the results for the 14th, I go and change it in the cell that I am using to store the date (remember it is a variable in my formulas) and I get 5 for Role 1 and 3 for Role 2.

Is it possible?

Thank you all for sharing your knowledge!

1
  • 1
    There are too many unknowns still. What is the sheet name of the raw data? What range is it in? Where should the results/report go? Which cell will hold the date? In the original data, are the dates real numerical dates or strings? The best way to receive helps is to share a link to a sample sheet that is set up as realistically as possible and where you have manually entered the results you want to see for the sample data set.
    – Erik Tyler
    Commented Jan 13, 2022 at 15:32

1 Answer 1

0

The answer comprises three parts:
1 - a Data validation (dropdown) cell where the date can be chosen
2 - a range that can be used for the Data validation cell
2 - an output range containing a query that references the dropdown cell.

Data validation cell (dropdown)

  • In the example below, this is cell F3. And the data Validation range = $I$4:$I$33
  • The green background is just to highlight the cell. It is NOT conditional formatting.

Range for the Data Validation

  • In the example below, this is cell I3. It contains this formula:
  • ={"Date List";query(transpose(B1:W1),"select Col1 where Col1 is not null")}

An output range

  • Enter this formula in cell F4.
    • =query({A2:C4},"select Col1, sum(Col"&(MATCH(F3,B1:W1,0)+1)&") group by Col1 label Col1 'Category', sum(Col"&(MATCH(F3,B1:W1,0)+1)&") 'Number of staff'")

Logic

  • Cell F3 contains a dropdown selection of the dates in Row#1 of the source data.
  • The data is created by transposing the date cells
  • The output cells are generated by the query.
    • the key is (MATCH(F3,B1:W1,0)+1)
    • this looks for a match of the date in Cell F3 (the dropdown) among the date values in Row#1
    • it returns an index number; add "+1" to the index to allow for Column A in the query range.
    • result is something like "Col2" or "Col3", etc so that the query will sum the appropriate column that matches the date selected in cell F3

SAMPLE

snapshot

Your Answer

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

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