SUMIFS, tab references and INDIRECT?

hochstas

New Member
Joined
May 25, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello,

I use a number of versions of the formula below on a spreadsheet on a sharepoint site that several people are working in each day. Rows are continuously deleted and data is continuously being pasted in.

=SUMIFS('Open Calls Report'!$P$2:$P$9978,'Open Calls Report'!$N$2:$N$9978,B27,'Open Calls Report'!$H$2:$H$9978,$C$24)

It works perfectly for my needs except for 2 problems:

  1. As rows are deleted and added, I continually lose range. I've taken to setting the ranges from 2 to 10000 so I don't have to fix the formulas as often. There's normally between 4-700 rows in the spreadsheet. I just did this this morning and already down to 9978. I heard that INDIRECT may solve that problem, but I've tried to modify my formula and I don't think I'm understanding the syntax correctly. This is the primary problem and I'd be thrilled if I could just keep the cells fixed between 2 and 1000.
  2. Secondary item that could save some time if fixable. The report that we run to paste additional rows into 'open calls' tab always has the values in column P as "numbers formatted as text". So each morning I have to open the spreadsheet in desktop version, highlight all of column P, click the little exclamation point box and choose "convert to number". Is there anything that can be added to the formula to add the number regardless of whether they're formatted as numbers or text?
Thanks in advance for the help. I'm an excel novice and it took me forever to research enough to figure out how to create the original formula.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,951
You don't really need to fix the range on a SUMIFS in my experience,

=SUMIFS('Open Calls Report'!$P:$P,'Open Calls Report'!$N:$N,B27,'Open Calls Report'!$H:$H,$C$24)

Not sure about the second issue.
 
Solution

hochstas

New Member
Joined
May 25, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
You don't really need to fix the range on a SUMIFS in my experience,

=SUMIFS('Open Calls Report'!$P:$P,'Open Calls Report'!$N:$N,B27,'Open Calls Report'!$H:$H,$C$24)

Not sure about the second issue.
Thanks! That solved my first issue!
 

Forum statistics

Threads
1,136,445
Messages
5,675,899
Members
419,591
Latest member
mersanko

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