Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIFS using a text range
I am still new to Excel 2007. I want to start using SUMIFS for their
efficiencies over array formulas. They don’t seem to work the same. I want to sort by Dep and Account and by month. The data comes in TEXT format. I can’t get it to work without converting the TEXT to VALUES. I bring in data and need to sort it like this. Dep AccountII AccountII SALARIES 110 60010 60999 SERVICES 110 65000 65999 MARKETING 110 70000 74999 TRAVEL 110 75000 79999 FACILITIES 110 80000 80109 OTHER 110 80110 81999 OTHER INCOME 110 82000 89999 SALARIES 120 60000 60999 SERVICES 120 65000 65999 MARKETING 120 70000 74999 TRAVEL 120 75000 79999 FACILITIES 120 80000 80109 OTHER 120 80110 81999 OTHER INCOME 120 82000 89999 This formulas works IF I convert the TEXT to VALUES. In 2003 I could sort on the TEXT with an ARRAY formula. With 2007 this formula works but only if I convert the TEXT to Values. =SUMIFS(Amount,AccountII,"="&$R10,AccountII,"<="& $S10,Dep,$B10,Month,F $2) It does not work if I leave the search columns (Account) as TEXT. Any solutions would be welcome and I thank you in advance. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIFS using a text rage | Excel Worksheet Functions | |||
SUMIFS using a text rage | Excel Worksheet Functions | |||
multiply criteria in a range using sumifs | Excel Worksheet Functions | |||
Using SUMIFS with date range | Excel Discussion (Misc queries) | |||
Sumifs with error value in the sum range | Excel Discussion (Misc queries) |