SUMIFS using a text range

[Ziggy]

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.

 

[Ziggy]

The AccountII columns are actually the "R" & "S" in the formula. The
"AccountII" is the lookup range. Then there is also a Dep (Department)
lookup range and a Month range.

This allows me to sort expenses By Department, by month and in an
accounting range.

The Dep and Month are not a problem becasue the represent a fixed
value.

The problem comes because I am looking into a range of text vaues. In
other words I am looking for account values between 60000 to 60999 and
65000 to 65999, etc as TEXT. These and the lookup range is what I have
to convert to VALUES. Since they come out from the DB as text I'd
prefer to leave them as text and avoid having to convert them to
VALUES.

 

[Ziggy]

I have managed to get the attached workbook working with your text
data.

I have modified the first 3 columns, to all show Month1, with
differing formulae, to prove that all 3 results are the same.

Column G has your original formula, looking at extra columns where you
have converted Text to Numbers

Column H, calculates the result using Sumproduct formulae. I am not
suggesting using this as a solution, because Sumproduct is much slower
than Sumifs.

Column F uses my revised formula

=SUMIFS(Amount,Account,">--"&$C6-1,Account,"<=--"&$D6,Dep,$B6,Month,F
$2)



This is using all of the original Text data.

I have coerced the Text to Numeric, using the double unary minus --


Roger Govier was good enough to send me this solution offline. I had
never seen the unary function before. Many thanks Roger.

It works exactly as I needed it to work.

Siegfried