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:
(SUMIFS(TEXT(AA61459):TEXT(AB61459),TEXT(AC61459):TEXT(AD61459),J61459)
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?
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:
(SUMIFS(TEXT(AA61459):TEXT(AB61459),TEXT(AC61459):TEXT(AD61459),J61459)
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?
