Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulas involving ranges
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulas involving ranges
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 "Brenda463" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formulas involving ranges
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 "SimonCC" wrote in message ... 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 "Brenda463" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How prevent formulas to get external references/path to current workbook? | Excel Worksheet Functions | |||
locking formulas?? | Excel Discussion (Misc queries) | |||
Need Formulas for counting multiple conditions | Excel Worksheet Functions | |||
compare unique identifiers in multiple ranges | Charts and Charting in Excel | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |