Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Sum Based On String


How to summing total base on certain string?
A sample is enclosed


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
String Value Sum Function Vba
I have a Sum function in a cell on a spreadsheet and a bit of code which checks it using an If statement. If the Sum equals 1 (100%) it should do one thing, if not do another. However, if I put in the If statement that the Sum cell value should equal 1, it doesn't work. If I put it should equal "1", it does work. Why?

Example that works:

If Range("LocalBirthTypes").Value <> "1" Then
MsgBox "To use your own data the split between the birth types must total 100%", vbExclamation, "Maternity service planning tool"
Goto Exiting
End If

Example that doesn't work:

If Range("LocalBirthTypes").Value <> 1 Then
MsgBox "To use your own data the split between the birth types must total 100%", vbExclamation, "Maternity service planning tool"
Goto Exiting
End If

View Replies!   View Related
Sum A Range If A Cell Contains A Text String
If I have a cell (a1) containing the text "Sage, Navision, Hardware" how do I write a sumif formula that will add up a set of numbers based on cell A1 containing the text string "Navision" (for example)


View Replies!   View Related
Calculate Sum Of Digits In A Number String
I need to calculate the sum of digits in a numeric string. The function should keep on adding the digits of each result, till the final sum is a single digit. Please refer to the following example and result expected:-

Supposed the number entered in Cell A1 is 456789

Result 1 = 4+5+6+7+8+9 = 39

Result 2 = 3+9 = 12

Final Result required = 1+2 = 3

View Replies!   View Related
Extract Data From A String Array And Sum
I want to extract data from array string and then sum the values. For reference attaching the excel.

View Replies!   View Related
Editing String Based On Length
I get all my results from a CSV file. This isn’t a problem except for the first nine days of the month where excel cuts off the first 0 so 01012009 reads 1012009 and cannot be properly concatenated.

DateNameItem
1012009BobKeyboard
1012009SidMouse
1012009DaveMonitor
1012009EdMonitor
1012009AndrewKeyboard
1012009DanKeyboard
1012009BruceKeyboard

What I would like to do is have VB read the contents of the date cell and if it is less than eight characters, edit the cell by adding a zero at the beginning of the string. This needs to be a one off process for all cells. Can anyone come up with code that might make this happen?

View Replies!   View Related
IF Statements Based On Text In A String
I want to return Y or N for each row where a certain word (lets say "Apple") appears anywhere in the reference cell. Therefore in the below example the first 3 rows contain apple (somewhere in that cell) so Y is returned. I can get it to work when apple isd the only thing in the cell but not if it appears with text before and/or after it.

ie:

A B

apple Y
apple orange Y
orange apple Y
pear N
grape N

View Replies!   View Related
Sorting Sheets Based On A Text String
I have a sheet named: tool

In this sheet I have a long list of data that i would like to have sorted out on different worksheets that needs to be created by the macro. The deciding factor for the sorting are found colum B. In this colum there are different text strings. I would like for the macro to look at this text string and copy the entire line over to the respective work sheet. the work sheet needs to be named according to the text string in colom b.

Furthermore in the sheet named tool the first 3 lines are headings and i would like all of these transfered to each of the new sheets.

View Replies!   View Related
If String And Formulas (calculation Based On The Selections)
in cell A1 the user will select from a drop down list 5.50, 11,22, 33, 55. in cell B1 user selects from the drop down list a number 1 - 9. now in cell c1 I wont it to do some calculation based on the selections.

If b1=3, then A1*.20, if B1=2, then A1*.30, If B1=1, then A1*.50 and if B1 is greater then 3, post 0, if B1 is empty, then leave c1 empty.

View Replies!   View Related
Action Based On Individual Values In String
I need to read a binary value and trigger an action based on whether the each value is 1 or 0.

So for binary 1001

1 switch on
0 switch off
0 switch off
1 switch on

Assuming i know how to switch on and off does anyone know how to make the determination based on individual characters in a binary number string?

View Replies!   View Related
Find Text In String Based On Pattern
I am wanting to find within a cell the following pattern "ANNNN" - "A" meaning any alpha character A-Z and "N" meaning any numeric 0-9. The pattern ANNNN could be anywhere within the text. If the cell contains text with that pattern the text would be deposited in the adjacent cell. Lorem ipsum dolor sit A1234 amet, consectetuer adipiscing elit. Mauris tristique. Cum sociis

View Replies!   View Related
Take Action Based On String Value In Selected Cells
I am trying to take some action based on the string values in selected cells. Here is my code to iterate through selected cells and count the occurances of certain string values:

Private Sub TakeAction()
Dim cel As Range
Dim sel As Range
Dim complete As Integer 'a counter for completed
Dim pending As Integer ' a counter for pending
Dim cancelled As Integer ' a counter for cancelled
Set sel = selection
For Each cel In sel
If cel.Text = "Some text" Then
complete = Complete +1
If cel. Text = "Some other text" Then
pending = pending +1
If cel.Test = "Yet some other text"
cancelled = cancelled +1
End If
End If
End If
Next cel

MsgBox complete
MsgBox pending
MsgBox cancelled
End Sub

View Replies!   View Related
Insert New Row With String Based On Conditional Statement
I've been working on this project to propagate certain products through multiple categories. I chose to use excel to assign multiple categories to each product. On sheet1 I have setup products (column a) and qualifiers in the following columns (color, model, etc). What I would like to achieve is for the user to select yes or no for each category column and and if yes then have the corresponding category breadcrumb string (from sheet2) inserted into one specific column in sheet3. As the user continues to select multiple "yes" from the category columns for that single product, those additional category breadcrumbs get inserted at the end of the list in sheet3.

View Replies!   View Related
Formula: Split A Text String Based On A Symbol
is there a function that will split a text string based on a symbol. I know how tyo use left and right, which are based on a set number of characters but I want to split based on a "/" mark. whats to the left of the "/" mark and whats to the right of the "/" mark. any ideas. an exaplme is: tom / tim. i want a formula that will put the word "tom" in a cell and another formula that will put "tim" in another cell.

View Replies!   View Related
Delete Row Based On Partial String Criteria
I have 2 columns A and B. The data is in column B.

What I'm trying to do is delete entire rows from my current selection if they do not begin with mailto:

View Replies!   View Related
Insert Row Macro Based On Text String
I'd like to adapt my macro so that it would insert a blank row after it detects the the first 16 characters of text as "'Closing Balance"; or it could even detect "'Closing" as the first 8 characters if it would be simpler

The Data Begins in Row 5 of Column A

My Current Macro is as below which I have adapted from another one I used

It is not working since it is detecting for the exact text "'Closing Balance" whereas the data registry would write "'Closing Balance as at 31/10/2009" , of which the "as at dd/mm/yyyy" portion would change every time a report is exported, but the first part "'Closing Balance" or even just the word "'Closing" will always be the same.

View Replies!   View Related
Return A True/false Based On A Cell Containing A String Vs A Value
how return a true/false based on a cell containing a string (text) vs a value?

I have a column where most cells are blank, some contain text, and others contain dates. I would like to select and manipulate only the ones that contain dates (or values).


View Replies!   View Related
Segregate Data Based On The The First String Of Each Security Description
a macro that will segreate data based on the the first string of each security description under the heading Security description and then sum the total on a separate row in the total column.

Starting row hearder in sheet "Raw_Data" start in row 6 and i would require the same in sheet "Result

Sheet "Raw_Data ...

View Replies!   View Related
Auto Generation Of String Based On User Input
Without using VBA, how can i generate strings say

x1
x2
x3
x4
x5

assuming user has inputed two numbers 1 and 5. I know you might have lots of questions like how the hell user input is coming when there is no usage of VBA.

View Replies!   View Related
Sort Pivot Table Based On Partial String...
I've got a group of data within a pivot table that I want to be sorted based on only part of the string. For example,

Standard Kitchen
Standard Master Bath
Standard Hall Bath
Optional Kitchen
Optional Basement Bath

First they would be sorted by whether they begin with Standard or Optional. Then within those groups, they would be sorted whether they contain Kitchen anywhere in the string. So that the order would be Standard Kitchens, Standard Anything Else, Optional Kitchens, Optional Anything Else.

View Replies!   View Related
Summarize Data Based On Partial String Matches
I have two questions:

a. Check the code below:

Dim news1 As String, news2 As String

news1 = "new"
news2 = "polygon"
Dim countie As Integer
For buddie = 2 To b1.UsedRange.Rows.Count
'If b1. Cells(buddie, 1).Value Like "new*polygon" Then
If b1.Cells(buddie, 1).Value Like news1*news2 Then
countie = countie + 1
n1.Cells(buddie, 10).Value = "test"
End If
Next buddie

I have 2 strings, news1 and news2, i need to use the like function to check the occurance of these two strings in all the cells, and just for testing purpose, im printing 'test' it out in another excel sheet.

How do i make that work? the commented line shows what exactly i want, only that i dont want to hard code the string values.

b. If i have a value in the cell:
/new/blahblah/anycra/polygon
how do i split it so as to assign two variables to have 'new' and 'polygon'.

View Replies!   View Related
Insert Column Macro Based Variable String Letter
I'm working on some code that's part of a userform. To illustrate what I need, I will give an example. A column letter, 'J' for example, is stored in colNum.Value taken from the userform. I need both a column inserted before column J, and data entered into that new column in row 2 (thus J2, which would now be blank).

View Replies!   View Related
Conditional Rank Based On Whether A Criteria Cell Includes A Specific Text String
I'm racking my brains as to how I can structure a formula to conditionally rank a value in an array against only those values in the array whose corresponding criteria cell includes a specific letter.

So for example I have a list of 12 values, say 126; 239; 0; 171; 162; 157; 130; 199; 122; 153; 0; 15.
Each of those values corresponds to a heading, say: CDE; DFE; FGE; DFE; ERD; DEA; BDF; DFB; CDE; CEF; CAB; FAB. As you will note some of the headings may or may not be the same and may or may not include the same letters in different orders.

How can I write a formula that ranks in ascending order a given value drawn from the above list (which will be in another cell but which in this case is, let's say, the first value: 126) only against those values whose heading includes a specific character, for example the character C (the character in question will vary and be defined in a specific cell).

As an added complication I need the ranking calculation to exclude any zero values. So in the above example what the formula needs to do is rank the value 126 against a sub-set of the whole array comprising only the values 126; 122; 153; 0.

The answer I need is 2 because, discounting the zero value, 126 is the second highest value.


View Replies!   View Related
How To Get Sum Based On Column
I'm working on something that I believe is simple for most of the people here but I'm not really familiar playing with formulas.

I wanted to add a column which gets the sum of the row if column header is equal to a particular value.

I attached sample excel file to further clarify my problem.

View Replies!   View Related
Sum Based On A List
An example:
In range A1:A10 I have a short list of account numbers.
In column C I have a longer list of account numbers, with corresponding amounts in column D.

Now I want to sum column D, but only if the account number exists in my A-range.


View Replies!   View Related
Sum Based On Corresponding Values
would like to sum certain cells in a column based upon a specific value in a nearby column in the same row (e.g. 1, 2, or3). is there a formula for this?

View Replies!   View Related
Sum Of Value Based On Name And Status
I have excel in the below format.

Sheet1  ABCD1NameDescStatusSpend2raviGood boy1103kumarAve3104raviGood boy1205raviGood boy2306kumarAVE1407sureshMED1508subMED3609raviGood boy27010raviGood boy38011raviGood boy120 Excel tables to the web >> Excel Jeanie HTML 4

I need sum of value based on name and status.

And also i need Descrption also.

It is possible in VBA.

Sheet1  FGHIJ1NameDESCStatus 1Status 2Status 32raviGood boy50100803kumarAVE40 104subMED  605sureshMED50   Excel tables to the web >> Excel Jeanie HTML 4

View Replies!   View Related
Sum Based On Category
Tried to explore with sumifs but failed terribly.. does anyone know a formula that can sum up figures in different columns under the same Category.

Have attached a sample xls..

View Replies!   View Related
Sum Based On Colour
i'm currently using some VBA code to insert a formula and sum certain rows. Which rows to sum is determined by the following line...

View Replies!   View Related
Sum Based On Condition
I want to total column C, but only where column B contains certain data, like add column C where column b contains abd so that i get the answer 7, Hope ive explained it right.

B C
abd 1
abd 2
bmr 3
abd 4
crc 2
crc 3
bmr 2

View Replies!   View Related
Sum Based On A Range
I want to be able to perform a sum based on a range using greater and less than operators. Attached workbook shows the example, I want to calculate the sum of all the numbers in column B where the values in column A are > 3 AND <9 i.e. 4, 5, 6, 7, 8.

As this is just an example I would also like to know how I can vary the criteria e.g. change from >3 to >4. Also bear in mind that column A may repeat the value e.g. 5 may appear more than once and I need all values of 5 to be included in the sum.

View Replies!   View Related
Sum Based On A Different Cell
attempting to create a spreadsheet that will do some basic record keeping for 6 rental properties I own.

I have the individual income listed out on an "Income Received" sheet These cells are d5,d9,d13,d17,d21,d25. They are the rental incomes for January. I would like for the program to separate out monies deposited into the bank from that left in cash. I am attempting to do this by entering the date into a cell for each of the rental amounts that were deposited into the bank. These cells would be d8,d12,d16,d20,d24,d28. I want excel to add only the rental amounts that have a date in these previous cells. I want excel to place that total into cell d30.

how to do this or where I can find more info? I have searched out SUMIF functions and can't seem to locate any help with that.

View Replies!   View Related
Sum Range Based On Value In Another
I have a sheet that is organized by having company names run down the A column, then it has corresponding data on the same row. EX: A1= Bobcat Co. A2= Customer Number (ie: 101) A3= Total Value (ie: $300). My problem is that there is 40 or so Bobcat entries and I need to be able to output a specific company's overall total value (they are scattered by purchase date) and output that to a different sheet. Do I need a code that would search all the A columns for the company name and then once it finds them add up their total value?

View Replies!   View Related
Sum Based On Criteria
I've got a sheet called DATA with a series of columns. Column Q has a series of numbers throughout the rows. I need to input a formula in a cell that says, everytime column Q is = 2, calculate the sum of those rows in column N.

The second one is a bit more challenging. A few cells in column F contain the number 1 in them. I need a formula that calculates the average of the cells in column C wherever there is a 1 in column F.

View Replies!   View Related
Sum Based On 2 Conditions
I want to have SUMIF and IF functions to be combined. i tried using the formula SUMIF( Timesheet!D2:D55,B2,Timesheet!F2:F55) but i want to edit the range everytime.
Can i have formula like if name = Ant merce and prj = Implementation support, then the sum will be 15 for the sumrange of Timesheet!F:F (entire F column)

View Replies!   View Related
Sum Based On 1 Criteria
I have 1 worksheet which consist of few products for 6 months which I want to
sum up the individual product cost for certain period ie. YTD for 3 month, YTD april, and Q2 (april to June). The result is to appear in another worksheet. Try to use sumproduct, but unable to get what i want..

View Replies!   View Related
Find And Sum Based On Criteria
I have a sheet with 3 columns - Line, Product and amount. I have to calculat the amount based on which line it is, and which product.

I. e I want to calculate all the amount in Line 1 except if the product is 3,23,31 (just an example - the range can be longer).

In workbook added i have shown the problem. I have tried the sumproduct formula, but cant get it right!

View Replies!   View Related
Get A Sum Total Based On Three Criteria
I've got a spreadsheet with headers in column A and in rows 9 and 10. The headers in column A start in row 11 and are sizes (i.e XS, S, M, etc..). The headers in row 9 start in column B and are names (ie. First, Second, Third). The headers in Row 10 can be either Net Sales or On Hand Units. There are then values starting in B11 to L19.

The headers in column A do not repeat but the headers in row's 9 and 10 can repeat. What I need to do is create a function for a report page that will total all of the values that match to a particular header in column A, the header in row 9 and the header in row 10. So for example I would want to get the total for any instance when the header in column A is M, the header for row 9 is Second, the header for row 9 is Net Sales.

I've attached a sample of what my data dump would look like as well as the separate reporting worksheet where I want the totals to appear.

View Replies!   View Related
Sum Values Based On Text
I need a formula that will calculate the sum total of 6 and a longer formula to cope with 12 letters in a range. The third value in both ranges is worth double points.

The letters and their corresponding values are:

F=0, P=2, M=4, D=6

Some 6 letter examples to illustrate:

in A3:D3 the letters D,D,D,D,D,D return the value of 42 in E3 (6+6+12 (third letter is worth double)+6+6+6
in A4:D4 the letters P, P, F, F, M, M return the value of 12 in E4

Some 12 letter examples to illustrate:

in A3:L3 the letters D,D,D,D,D,D,D,D,D,D,D,D return the value of 78 (6+6+12+6+6+6+6+6+6+6+6+6)

If the range is empty the formula should leave the cell blank.


View Replies!   View Related
Sum Up Values Based On Date
I am looking for a way to sum up values segregated on specific dates.

I like to sum up values from column C based on dates in column A. Data I have to look at are based on groups of 7 weekdays. Data to be sum up always goes from Monday too Sunday

Example: If date is equal to or lower than 31-12-2009 then sum values C2:C5 and if date is equal to or larger than 01-01-2010 then sum values C6:C8.

A

B

C

1

Date

Weekday

Value

2

28-12-2009.......


View Replies!   View Related
Conditional Sum Based On Two Columns
I have a little problem I would like some assistance on. I pull data from a data warehouse into an Excel spreadsheet that is linked to another spreadsheet that consolidates the data. I need to add the PGL Grads only for completed classes which are usually show zero (Actual Grads column) unless there were no entries (Actual Entries). Currently, the formula used is =SUMIF($I$104:$I$118,">0",$H$104:$H$118) but this can cause problems if there is a zero value for a completed class. In the example below, the total for PGL grads is 142 but in reality it should be 158, the zero value for class 2008008 skewing the result. I have tried variations of SUMIF, SUMPRODUCT, and SUM and end up with the same results. The warehouse data puts zero values in the Actual Grads column for classes that have not completed and I know I can make this work with blank values but that just creates another step in the process ...

View Replies!   View Related
Progressive Sum Based On Variable
ok, this may be the same answer as the previous post i did, but if so, i can't figure that part out.

In O2, if M2 = 8, and N2 = 2008, then add O3 + 8 rows (O3:O10)

or, if M2 = 5, and N2 = 2009 then add O16 + 5 rows (O16:O20).

View Replies!   View Related
Sum 1 Column, Based On 2 Variables
A B C D
COL 0
COL 0 1
COL 0
COL 0 1
COL 0
EDM 0

I need to add the number of 0's in column B if Column A is COL and C OR D is 1. correct function yields 3 for this example. I've played with =sumproduct(if)) and =sumproduct(if(and))) etc... can't figure it out.

View Replies!   View Related
Sum Based On Conditional Formats
I have to color direct payoffs and commissions (green and blue respectively). I have to have a running total of payoffs that have not cleared and one for commissions. I have set up conditional formatting to color the text based on the check classification (A for payoffs and C for commissions). What I am trying to do is write a formula that will count only the payoffs (green text) that do not have a cleared date. I am then trying to do the same for the commissions (blue text). these totals are needed separately so I do not need them combined.

I have tried sumif and sumproduct. I have since deleted the formulas i have written cause they did not work.

View Replies!   View Related
Sum The Numbers Based On If There Is A Date
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 12.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutG1G2B4C4D4E4F4G4=
ABCDEFG104/30/07$107,777$0*$0$0$107,777209/06/07-$107,777$227,399*$98,962$91,165$309,7493???$28,046$170,458$0$86,434$18,243*4*$28,046$397,857*
$185,396$109,408$417,526Sheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View Replies!   View Related
Sum A Range Based On A Cell Value
I am looking fo a simple way to carry out the following..

My Spreadsheet has a range of data from column D to O

I have a Total Column Q

I have a Cell, eg A2, where I enter a value from 1 to 12

Depending upon the value entered into A2 will dictate the range of cells to sum, ie

A2 = 4 - Column Q would Total Range D?? to G??
A2 = 12 - Column Q would Total Range D?? to 0??
etc.

View Replies!   View Related
Sum Based On Most Recent 12 Months
I would like to sum a range of data based on the most recent 12 months.

Details:
Row E9:?9 - Contains random dates
(where "?" will change based on the user will be min 12 and max undefined)

Column C - Where I want the sum formula - to sum that corresponding rows data based on the most recent dates listed in row 9.

View Replies!   View Related
Sum Based On Multiple Conditions
I am trying to sum numbers based on three counditions.

My problem the third condition needs to use a wild card. I have attached a sample file, in the last condition I am only wanting to sum numbers where the characters in the last column begins with bd*

The yellow cell contains the formula I am using.

View Replies!   View Related
Sum Based On Date Range
I would like to have cell G3 automatically sum the values in Column B based on the start and end date parameters that are typed in the cells (E3 and E4).

View Replies!   View Related
Sum Tabs Based On Criteria
I need to create the formula in "Sum state" sheet B2 to sum items for tab AZ (b2:b6). The formula needs to work when I drag it down to other cells.
For example if I drag formula to B3 it needs to adjust the tab from AZ to CA, and add things for that tab. Each cell from A2 :A 11 represent a state and each tab has actual info for each state

View Replies!   View Related
Add / Sum Based On Dates
The object is to automate a weekly time sheet based on daily information. The "Daily Time" worksheet contains all 365 days of the year. The work week runs Monday through Sunday. Daily time is posted to the "Daily Time" sheet from handwritten time cards. The Weekly Time is then automatically calculated for each person and each category based on the dates in the "Pay Period From:" field and "To:" field.

For example if 01/01/09 (Monday) is in the "Pay Period From:" field and 01/07/09 (Sunday) is in the "To:" field, then the "Weekly Time" sheet will total the hours posted between those two dates. In this manner there will be a continuous record of daily time in the "Daily Time" worksheet and the "Weekly Time" worksheet will reflect whatever dates are used in the two fields. I realize that because it is simply two dates, it is possible to show any combination of days.

Basically what I need is to be able to add the hours between two dates and obviously the dates will change on a weekly basis. EXAMPLE IS ATTACHED

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved