Using the AND function with SUMIF??

[cdiffell]

Here's a thorny problem:

I'm using SUMIF in the usual way, but the criteria I'm using are account numbers 17 digits long. Unfortunately, 15 digits is the limit that SUMIF looks. So what I'd like to do is use the first 5 digits and the last 7 digits in the account number as my criteria for the SUMIF function. I suspect this will involve the AND and LEFT and RIGHT functions, but I've tried for a while to no avail.

Any suggestions??

[Aladin Akyurek]

On 2002-05-24 11:29, cdiffell wrote:
Here's a thorny problem:

I'm using SUMIF in the usual way, but the criteria I'm using are account numbers 17 digits long. Unfortunately, 15 digits is the limit that SUMIF looks. So what I'd like to do is use the first 5 digits and the last 7 digits in the account number as my criteria for the SUMIF function. I suspect this will involve the AND and LEFT and RIGHT functions, but I've tried for a while to no avail.

Any suggestions??

You'll need to switch to SUMPRODUCT as in

=SUMPRODUCT((A1:A10=E1)*(B1:B10))

where E1 houses a 17-digit acc# with text underlying format.

I might add that I'm surprised to see SUMIF to fail here.

[cdiffell]

Thanks for the help. I tested it out, and it looks like SUMIF only goes out to 15 digits. Just for future reference

[cdiffell]

Thanks for the help. I tested it out, and it looks like SUMIF only goes out to 15 digits. Just for future reference

[Aladin Akyurek]

On 2002-05-24 11:49, cdiffell wrote:
Thanks for the help. I tested it out, and it looks like SUMIF only goes out to 15 digits. Just for future reference

Are you sure?

Anyway you still need the SUMPRODUCT function:

=SUMPRODUCT((LEFT(A1:A10,5)=LEFT(E1,5))*(RIGHT(A1:A10,7)=RIGHT(E1,7))*(B1:B10))
where E1 has a 17-digit acc# with text as underlying format.

Addendum: See the figure below.

Microsoft Excel - Book2 ___Running: xl2000 : OS = Windows (32-bit) NT 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
A1 = 12345678901234567
	A	B	C	D	E
1	12345678901234567	5	:alert('=SUMIF(A1:A3,E1,B1:B3)')>13		12345678901234567
2	12345678901234568	3	:alert('=SUMPRODUCT((A1:A3=E1)*(B1:B3))')>10
3	12345678901234567	5	:alert('=SUMPRODUCT((LEFT(A1:A3,5)=LEFT(E1,5))*(RIGHT(A1:A3,7)=RIGHT(E1,7))*B1:B3)')>10
4
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.27]
If you want FREE SOFT, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


Aladin



[ This Message was edited by: Aladin Akyurek on 2002-05-24 12:07 ]