Can't calculate a spill range within a formula

jgalin

New Member
Joined
Nov 24, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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?
 

Attachments

  • Excel Error - Dynamic Ranges.jpg
    Excel Error - Dynamic Ranges.jpg
    164 KB · Views: 7

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

jgalin

New Member
Joined
Nov 24, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,251
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 24, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,251
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,973
Messages
5,656,174
Members
418,284
Latest member
LaurentT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top