I have two named ranges in a table, 'AccNo' and 'AugustP'. Both
ranges are equal in length and occupy the same rows. 'AccNo' may have
the same value in more than one cell in the range.
My formula, =-SUMIF(AccNo,$B6,AugustP) works well where cell B6 equals
a cell in the named range 'AccNo', typically the number 5000.
I want to change the value of those cells in 'AccNo' with a text value
of say 5000-01, keep the value in B6 and still retain the value
produced by the original formula.
I have tried the formula =-SUMIF(LEFT(AccNo,4),$B6,AugustP) but Excel
tells me there is an error in the formula. I have made sure the cells
in 'AccNo' and B6 are formatted as text.
Has anybody any suggestions?
Thanks for any help offered,
Dave Moore

we heard you the first and the second time.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"DaveMoore" wrote in message
oups.com...
I have two named ranges in a table, 'AccNo' and 'AugustP'. Both
ranges are equal in length and occupy the same rows. 'AccNo' may have
the same value in more than one cell in the range.
My formula, =-SUMIF(AccNo,$B6,AugustP) works well where cell B6 equals
a cell in the named range 'AccNo', typically the number 5000.
I want to change the value of those cells in 'AccNo' with a text value
of say 5000-01, keep the value in B6 and still retain the value
produced by the original formula.
I have tried the formula =-SUMIF(LEFT(AccNo,4),$B6,AugustP) but Excel
tells me there is an error in the formula. I have made sure the cells
in 'AccNo' and B6 are formatted as text.
Has anybody any suggestions?
Thanks for any help offered,
Dave Moore