SUMIF and LEFT and Named Ranges
 

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

SUMIF and LEFT and Named Ranges
 

=-SUMPRODUCT(--(--LEFT(AccNo,LEN(B6))=B6),AugustP)

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

SUMIF and LEFT and Named Ranges
 

Thanks for this Bob.
Sorry about the duplicated postings.
On reflection, I think it may have happened when I hit the back and
forward buttons on my browser. Won't do that again!

It works! (as if you had any doubts). Why does it work? What do the
double hyphens do?

Bob Phillips wrote:
=-SUMPRODUCT(--(--LEFT(AccNo,LEN(B6))=B6),AugustP)

SUMIF and LEFT and Named Ranges
 

It works by creating an array of values from the AccNo range that are
stripped down to the same number of characters as B6.

The LEFT(AccNo,LEN(B6)) will return an array of string values, so the --
before that is used to coerce these values into numbers, which are then
compared against B6, --LEFT(AccNo,LEN(B6))=B6. This returns an array of
True/False, so the other -- is used to coerce this to an array of 1/0, which
is then multiplied by the range AugustP to get the sum of matching values.
--
HTH

Bob Phillips

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

"DaveMoore" wrote in message
ups.com...
Thanks for this Bob.
Sorry about the duplicated postings.
On reflection, I think it may have happened when I hit the back and
forward buttons on my browser. Won't do that again!

It works! (as if you had any doubts). Why does it work? What do the
double hyphens do?

Bob Phillips wrote:
=-SUMPRODUCT(--(--LEFT(AccNo,LEN(B6))=B6),AugustP)