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?
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?