# Using a function in sumifs criteria range returns a string and breaks the whole formula

#### DHero

##### New Member
So first off let me explain and establish a couple things.
1: The way I understand it the sumifs function works this way: =SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], ...)
An example of it being used(A small part of my formula): (SUMIFS(\$J\$61381:\$J\$61498,\$K\$61381:\$K\$61498,J61459)
So all well and good, I can use it and drag across some cells and define a range, sum how many things there correspond with J61459 and in the end have about say "2" things. Then I can go on and drag it again some cells below and keep doing it thousands of times because that is time well spent!
Well obviously I will not do that, so here is the second point:
2: The spreadsheet I'm talking about has 60k+ rows, and it is divided by days of the year, what I want is to search fow how many "things" are there in every day, separatedly and, not wanting to drag and drop my mouse for the next two years non-stop I devised a way that checks whenever the date of the cell above is different, and using cell(address) I have a bunck of cells that look like this:

Those are the addresses for the upper and lower limit of the ranges of start and end of every date on this spreadsheet and what I wan to do is to replace this:
(SUMIFS(\$J\$61381:\$J\$61498,\$K\$61381:\$K\$61498,J61459)
With this:
I mean you get it right? I know it is written kinda worng but if it worked I would only have to drag it from the bottom of the spreadsheet to the top once, andit would still count all those "things" in every day! Now here is what I've gathere as to what may be my problem, while analysing the formula I can see that in the end what ends up being actually "written" on the cell is something like this:
(SUMIFS("\$J\$61381":"\$J\$61498","\$K\$61381":"\$K\$61498",J61459)
As expected from using the text function, what I get in the end is text and it breaks sumifs, and probably every other function in excel too. I've tried it with CELL("address",J61459) and tried to convert the text to number somehow (with VALUE and CLEAN) to no avail. So here is the problem, can I somehow use this logic with some function or way(without using macros) in excel that pulls the addres of the cell form those in the image and uses this "text" address as parte of the criteria range without problems?

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### Special-K99

##### Well-known Member
TEXT(AA61459):TEXT(AB61459)
try

edit ignore

#### Special-K99​

posted just as I hit the post
indirect() may help you as it uses the text in a cell and adds to a formula

#### DHero

##### New Member
TEXT(AA61459):TEXT(AB61459)
try
Will try and get back to you, just a sec

#### DHero

##### New Member
TEXT(AA61459):TEXT(AB61459)
try
It worked!! Gdammit thank you so much!

Replies
8
Views
390
Replies
12
Views
148
Replies
3
Views
147
Replies
6
Views
563
Replies
2
Views
246

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,467
Messages
5,831,802
Members
430,087
Latest member
meagerd

### 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.

### Which adblocker are you using?

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

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