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

DHero

New Member
Joined
Sep 22, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
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:
this.PNG

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?
 

Excel Facts

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

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,424
Office Version
  1. 2019
Instead of
TEXT(AA61459):TEXT(AB61459)
try
(SUMIFS(INDIRECT(AA61459&":"&AB61459),INDIRECT(AC61459&":"&AD61459),J61459)
 
Solution

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,617
Office Version
  1. 365
Platform
  1. MacOS
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
 
Master Excel Bundle

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.

Forum statistics

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