Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF Function
Dear all
I have to add and subtract the values of some cells.So that's the case In the first column i have the codes of account which have in the first part 4 numbers underscore and some other figures. (1000_A1, 1000_A2, 1050_A3, 2000_A4). So based on the first four numbers i want to add those that have 000 and subtract those that include 50 (eg lets say that the third character to be 5 and the fourth characted to be zero). So i need to add 1000_a1 + 1000_a2 + 2000_a4 and sub tract the 1050_a3 I thouhg to use one sumif to add those include 000 and another one to sumif with 50.The problem is that i do not know how to set up the if match in SUMIF. Any suggestions? Thanks in advance Manos |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF Function
Maybe one way:
=SUMPRODUCT(--(MID(A1:A4,2,3)="000")-(MID(A1:A4,3,2)="50"),LEFT(A1:A4,4)*1) HTH, Paul -- "Manos" wrote in message ... Dear all I have to add and subtract the values of some cells.So that's the case In the first column i have the codes of account which have in the first part 4 numbers underscore and some other figures. (1000_A1, 1000_A2, 1050_A3, 2000_A4). So based on the first four numbers i want to add those that have 000 and subtract those that include 50 (eg lets say that the third character to be 5 and the fourth characted to be zero). So i need to add 1000_a1 + 1000_a2 + 2000_a4 and sub tract the 1050_a3 I thouhg to use one sumif to add those include 000 and another one to sumif with 50.The problem is that i do not know how to set up the if match in SUMIF. Any suggestions? Thanks in advance Manos |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIF Function
Thank you very much
"Manos" wrote: Dear all I have to add and subtract the values of some cells.So that's the case In the first column i have the codes of account which have in the first part 4 numbers underscore and some other figures. (1000_A1, 1000_A2, 1050_A3, 2000_A4). So based on the first four numbers i want to add those that have 000 and subtract those that include 50 (eg lets say that the third character to be 5 and the fourth characted to be zero). So i need to add 1000_a1 + 1000_a2 + 2000_a4 and sub tract the 1050_a3 I thouhg to use one sumif to add those include 000 and another one to sumif with 50.The problem is that i do not know how to set up the if match in SUMIF. Any suggestions? Thanks in advance Manos |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to nest a left function within a sumif function? | Excel Worksheet Functions | |||
Using the TODAY() function in a SUMIF function | Excel Worksheet Functions | |||
How do I use the TODAY function with the SUMIF function? | Excel Worksheet Functions | |||
SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |