1

I'd like to find a generic way to see when resources are available, in order to help plan when activities can be run. In this case, the resources are people with certain instructor qualifications (climbing, canoeing, etc.) and equipment such as mini-busses, canoe trailers, climbing kit, etc.

The resource data would look something like this:

Resource  | CWI | RCI | D1  | PI1 | PI2 | Bus | continued below -->
----------+-------------------------------------------------
PersonA   |  1  |  1  |     |  1  |  1  |     |
PersonB   |  1  |     |  1  |     |     |     |
PersonC   |     |     |  1  |  1  |  1  |     |
PersonD   |  1  |  1  |     |     |     |     |
PersonE   |     |     |     |  1  |     |     |
PersonF   |  1  |     |  1  |     |     |     |
MiniBus1  |     |     |     |     |     |  1  |
MiniBus2  |     |     |     |     |     |  1  |

Then the date data would be over to the right, like this:

<-- cont... | WE1 | WE2 | WE3 | WE4 | WE5 | ...for each weekend of the year
------------+-------------------------------------------------
(PersonA)   |  1  |  1  |     |     |  1  | ...
(PersonB)   |  1  |     |  1  |  1  |  1  | ...
(PersonC)   |     |     |  1  |  1  |  1  | ...
(PersonD)   |  1  |     |  1  |     |     | ...
(PersonE)   |     |  1  |  1  |  1  |  1  | ...
(PersonF)   |  1  |     |  1  |  1  |     | ...
(MiniBus1)  |  1  |  1  |  1  |  1  |  1  | ...
(MiniBus2)  |  1  |  1  |     |  1  |  1  | ...

At the bottom of each weekend column, I want to count how many mini-busses, Climbing Wall Instructors, Rock Climbing Instructors, D1 drivers, etc. are available that weekend, then I could use that information to determine which weekends different activities are viable, for example, if I need a mini-bus and 2 RCIs I can see which weekends have that.

    | WE1 | WE2 | WE3 ...
RCI |  2  |  1  |  1
Bus |  2  |  2  |  1
etc.|     |     |

So the formula needs to count the number of resources in the column that has a 1 (meaning available), but it needs to only count it if a different specified column on that same row (relating to the qualification or resource type) also has a 1.

1
  • cell C13: ={I1:N1}
  • cell B14: =TRANSPOSE({B1:G1})
  • cell C14 (and drag to the right and then drag down):

    =COUNTA(IFERROR(QUERY(ARRAYFORMULA(TRIM(TRANSPOSE(QUERY(TRANSPOSE(
     IF($B$2:$N$10=1, $B$1:$N$1, )), , 999^99)))),
     "where Col1 contains '"&C$13&"' 
        and Col1 contains '"&$B14&"'")))

    0

  • 1
    Well this works - it took me a while to figure out what was going on, and to change the references for my data, but it works :) – jhabbott Apr 17 at 22:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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