# SUMIFS, tab references and INDIRECT?

#### hochstas

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

#### hochstas

##### New Member
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!

Replies
3
Views
117
Replies
1
Views
473
Replies
1
Views
263
Replies
5
Views
378
Replies
4
Views
159

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.

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