Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If I add v8 (Col A) and v8(col B), what formula to get v16(col c)
In a row the values would ranging from v4 to v8, how would I add all the
values that starts with v? So if col a would appear v8, column b would be v8 and column c would be s8, how will I add only the columns that starst with v? Thus, I would need the sum in column f showing v16? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If I add v8 (Col A) and v8(col B), what formula to get v16(col c)
Stacy,
You can use the following SUMIF formula which will sum all values in row 2 where cells beginning with V. =SUMIF(1:1,"V*",2:2) To create the values in row 2 you could use the follow formula: =VALUE(REPLACE(A1,1,1,"")) which converts i.e. V8 to 8 If you would like to sum the values after V in a single row then there is no way to do by regular Excel formula. You can use the folliwng UDF: Public Function mySumif(rng As Range, strFilter As String) as double For Each cell In rng If cell = "" Then 'Skip ElseIf InStr(1, cell, strFilter, vbTextCompare) < 0 Then res = res + CDbl(Replace(cell, strFilter, "", 1, 1, vbTextCompare)) End If Next cell mySumif = res End Function "Stacy" wrote: In a row the values would ranging from v4 to v8, how would I add all the values that starts with v? So if col a would appear v8, column b would be v8 and column c would be s8, how will I add only the columns that starst with v? Thus, I would need the sum in column f showing v16? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If I add v8 (Col A) and v8(col B), what formula to get v16(col c)
As long as every cell that contains a "v" also contains a number. Try this
array formula** : =SUM(IF(LEFT(A1:C1)="v",--MID(A1:C1,2,5))) I'm guessing that "v" means Vacation, "s" means Sick for timesheet calculations. ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Stacy" wrote in message ... In a row the values would ranging from v4 to v8, how would I add all the values that starts with v? So if col a would appear v8, column b would be v8 and column c would be s8, how will I add only the columns that starst with v? Thus, I would need the sum in column f showing v16? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|