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:
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:
- 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.
- 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?
