formulas involving ranges

[Guest]

I am trying to create a formula that will read a range of credit card numbers
and separate out M/C and Visa, then look at the amount in the corresponding
line different column, and put the total into a given spot for visa and
another spot for M/C.
I know that I can seperate using less than or greater than 5000 0000 0000
0000.
What I don't know is will the computer read this number with the spaces as a
number.
I cannot use an array, because the person who plugs in the data is not very
excel literate, and could not handle changing the formula everytime. The
finished report comes in printed format, on a piece of paper.

 

[Guest]

If you have cc numbers from A1:A100 and amount from B1:B100, then try:
=SUMPRODUCT(--(VALUE(LEFT(A1:A100,4))>5000),B1:B100)
and
=SUMPRODUCT(--(VALUE(LEFT(A1:A100,4))<5000),B1:B100)
for the two different type of cards.
Adjust the reference range as necessary.

-Simon

 

[Biff]

One card number begins with a 5 and the other begins with a 4.

=SUMPRODUCT(--(LEFT(A1:A100)="5"),B1:B100)

=SUMPRODUCT(--(LEFT(A1:A100)="4"),B1:B100)

Biff