Can't calculate a spill range within a formula

[jgalin]

I'm new to the spill ranges world and I got an error that I'm not understanding how or why. From the attached image, you can see a simple table with activities A, B, C, D and E. Each activity occurs at different dates. Some activities could last more than one day and activities can repeat in a different date. My ultimate goal is to represent these dates in a Gantt diagram, where I can put an "X" for each activity under the corresponding date, as shown in the image. As you can see, some activities are repeated in the table having different dates which means that I want to put the X in the same row of such activity multiple times in the same row.

I came up with a way to calculate that but I'm finding an error. I simplified the problem to illustrate it with one formula only. Here is what I have in each cell:

B2 = Input table with values
F3 = Spill range to sort the [Activities] column from the table
G7 = Value to use at the formula in cells H7 & H8 (just text, you can change it manually)
H7 = Result from the formula shown in cell I7. Here I used F3# to bring the spill range into the formula.
H8 = Formula resulting in error

Now, here is what I wanted to do:
1) I sorted the activities in F3, to get the spill range F3#. I used the formula =SORT(Table1[Activity]). I just did this to bring the spill range and illustrate the problem.
2) I calculated the number of times you find certain activity (the one in cell G7) in the spill range, by using the formula =COUNTIF(F3#,G1)
3) In H8 used the same formula as H7 but I tried to substitute F3# with the formula to sort the activities SORT(Table1[Activity]), resulting in the error shown in the image.

I'd like to understand why COUNTIF can't read the formula SORT(Table1[Activity]) as a range, if the result itself from that formula is a range.

Is there a workaround?

 

[jgalin]

I forgot to mention, what I actually want is to calculate the formula in H8 without actually showing the results of the spill range in F3 as an intermediate step. I'd like the formula to calculate the spill range in the background... if that even makes sense.

 

[Fluff]

Countif requires a range, rather than an array, which is why it doesn't work.
However I don't understand why you are trying to sort the data.

 

[jgalin]

Countif requires a range, rather than an array, which is why it doesn't work.
However I don't understand why you are trying to sort the data.

Thank you Fluff

I actually made a quick example just to illustrate the problem I am facing. In this case I just sorted it to get a spill range. But I could have used any other function. At the end, what I wanted was to insert the formula to calculate the "spill range" (I'm not even sure if I have to call it that way) into the COUNTIF function without the need to do an intermediate step by actually putting the result of the array in a specific cell, to then use it as a range in the COUNTIF function. Am I making sense?

As I mentioned, I'm new to the array/range world. To me, they where the same thing but after your answer, I did a little research and I think I understand a little bit better the difference between them.

Fortunately, I found a workaround for my formula using the MATCH function, but still, I'll do some more research about arrays/ranges.

Thanks for your help!

 

[Fluff]

Glad you sorted it & thanks for the feedback.