# 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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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

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

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

Replies
4
Views
341
Replies
7
Views
321
Replies
3
Views
956
Replies
3
Views
264
Replies
3
Views
507

1,217,356
Messages
6,136,078
Members
449,988
Latest member
Mabbas

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