Multiple Sums Depending On A Description
Dec 28, 2005
I really don't know how easy or difficult it might be, but here it is:
I have something like this (example) :
--------------------------------------
| A | B | C | D |
--------------------------------------
1 | 15| W1 | | |
2 | 5| W2 | | |
3 | 7| W1 | | |
4 | 9| W3 | | |
5 | 21| W4 | | |
6 | 14| W2 | | |
--------------------------------------
I want to have the result of all the W1 in D1, All W2 in D2, All W3 in D3 and all W4 in D4 without having to create any additional SUMs somewhere else to discriminate and then get the result (e.g. =IF($B2="W2",$A2,0) and copying the formula all along the column, then =SUM(xx:xx) and get the result.)
View 9 Replies
ADVERTISEMENT
Aug 28, 2007
I need to write a couple of functions. Firstly I want to do a count on column B (project $) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].
View 4 Replies
View Related
Feb 2, 2009
I've been struggling to figure out why my Conditional Sum formulas using multiple criteria keep reporting zero. I am tracking resource loads for a project which is divided into 4 parts (Iterations). I am trying to sum the total resource allocations per Iteration. see attachment!
View 9 Replies
View Related
Sep 2, 2007
I have a worksheet of hockey player statistics that I need to tally the Career Totals and the Totals for One Specific Team. Since each player has played a different amount of years in both their career and by team I cannot use a simple Sum formula as each player will have a different amount of rows to calculate and the rows may not be congruous.
The statistics also use text "--" to specify when a player did not play and "0" for when they did play but no statistics were registered. When the totals are calculated, I need to know whether they were registered or simply didn't play (i.e retaining the "--" in the totals instead of "0" if they did not play in those specific years). One example is listed, while the rest need filling in... attached is a partial sample of the data
View 7 Replies
View Related
Jun 12, 2014
I need to extract numbers from excel cells. For eg-
BAR TBG 04.00X02.25X26.50 1340 HRN SMLS SPEC. ES4.38694
^^^ This is one of the cells. I need to pull out 04.00, 2.25, 26.50, 1340 into different cells from one given cell.
I have tried using =MID() function, but I need to make changes into that formula everytime the position of first numeric letter changes while I go along a column. And I need to make changes into it as I go along the row.
It is taking me a lot of time in extracting numbers from one cell into different cells and I am afraid that I will spend the rest of my life getting this stuff done.
View 9 Replies
View Related
Mar 23, 2014
I have a spreadsheet with employees and data listed. The drop-down in A1 lets someone select the employee and then it hides the rows for all other employees. I want to add the names of supervisors in the drop-down of A1 and have it select only the employees under that supervisor and hide the rest. The number of employees under each supervisor ranges from 3 to 6. This is what I have to hide the rows when selecting a single employee :
[Code] .........
My workbook has stats data on the "Master" sheet(sheet #1) and analysts and supervisors on "Analysts" sheet(sheet #12). Data validation for cell A1 on "Master" sheet has all the analysts and supervisors in the first column of "Analysts" sheet with title "Select Analyst" in cell A1. I modified the "Analysts" sheet to show supervisors from B1:I1 and listed the analysts in the appropriate columns below them. Not sure how to make it select the analysts when someone selects the supervisor on "Master" sheet .
View 6 Replies
View Related
May 20, 2014
I have the following issue:
I have a table of data, and would like my userform to select parts on this depending on different variables.
When I run the macro "Show_UserFormCalculatePrice" or click the button "Calculate Price", the userform is activated which has 2 comboboxes, Date and Service.
When a certain date a service are selected I would like the the dish name to show in the labels of the userform.
My main issue is that there will be multiple dishes that will correspond to the data, and I would like them to be vertically listed in the labels depending on their number (Column A).
Essentially I would like to run some sort of If function:
If the date and the service correspond to the criteria then they are shown in the labels.
VBALabeling.xlsm
View 3 Replies
View Related
Oct 1, 2013
I need to build a formula to have one cell display multiple options depending on a value in another box.
If Value is >x and <x display Y
Details:
Cell D5 Holds a dollar amount. E5 is where the calculation will happen. Logically i need it to do the following exactly:
If D5 is between 5000 and 9999, display 75. If D5 is between 10000 and 14999 display 150. If D5 is between 15000 and 19999 display 200. If D5 is >20000 display 250.
View 2 Replies
View Related
Nov 15, 2013
I have a sheet with 5000+ entries, column A is an ID number, column B has one of 4 outcomes C,D and E are just times and quantities and are not important.
In column A there could be multiple entries of the same ID number.
In column B it will show Pcid243A, Pcid243B, Ddar400A or Ddar400B.
I need to export (to another sheet) any entries with the following conditions:
A = ID number with any instance in B showing both a 243 and 400 condition.
E.G
A B C D E
AA565677878D - Pcid243B - C - D - E
AA837632823G - Pcid243B - C - D - E
AA837632823G - Pcid243A - C - D - E
YA74846CC - Ddar400A - C - D - E
WW768765364F - Ddar400A - C - D - E
WW768765364F - Ddar400B - C - D - E
WW768765364F - Pcid243B - C - D - E
AA73262362G - Ddar400B - C - D - E
In the example above, I'd need the red cells exporting to a new sheet as they meet the conditions of having the same ID and both a 243 & 400 outcome:
A B C D E
AA565677878D - Pcid243B - C - D - E
AA837632823G - Pcid243B - C - D - E
AA837632823G - Pcid243A - C - D - E
YA74846CC - Ddar400A - C - D - E
WW768765364F - Ddar400A - C - D - E
WW768765364F - Ddar400B - C - D - E
WW768765364F - Pcid243B - C - D - E
AA73262362G - Ddar400B - C - D - E
Now I've spent ages trying different things and can't get anything to work...
View 3 Replies
View Related
Aug 2, 2007
In what would be Cell E2 I want to post the Total posted set Assuming that the Order Numbers and the OP Numbers are the Same, but only if there is a value in the allowed set, So I'd expect E2 in the case below to read 1.83,
Order No OP Posted Set Allowed Set
30761157 00100.731.500
3076115700100.500.000
3076115700100.600.000
3076461000100.000.000
3076461000101.050.500
3076524400100.000.000
3076524400100.550.500
3076639600100.000.000
3076639600101.180.500
3076810900100.000.000
3076862900100.000.000
3076862900100.000.000
3076862900100.000.000
View 9 Replies
View Related
Jan 27, 2014
In a macro I want to automatically insert a formula in several cells. The formula depends on other cells, and I want to be able to manipulate these cells with variables. Here is my code:
[Code] .....
All my variables are declared:
Sheets("DATOS YTD 2014").Cells(I, Semana + 1) is the cell I want to be able to chose due to the variables
Sheets("DATOS YTD 2014").Cells(I, 3) is the reference cell for the VLOOKUP formula, that will vary with I
SheetMonth.Range("B3:W172") is a range in the sheet SheetMonth which is fixed
The error message I receive is the following: "Run-time error 13, Type mismatch"
I precise that the next step is to make the ",6," part in the vlookup formula also variable... but I guess once my current issue is solved this one will be a piece of cake.
View 3 Replies
View Related
Jul 6, 2014
I have lot of data in Excel 2010 which I wish to bring in Columns using a Macro depending on the input value which the macro should prompt me. For E.g.:
A1B1C1D1E1F1G1H1I1J1
12345678910
12345678910
12345678910
12345678910
12345678910
If I select data from A1 and J1 (in practical it will be more Columns) the Macro should prompt me how many Columns would be the output on Master Sheet. If the input is 2 then it should create an output Sheet "Master" and should show the following result
A1B1
12
12
12
12
12
34
34
34
34
34
56
56
56
56
56
.. ..
.. ..
It after selection I run the macro and input 3 then the output should go in three columns (A1,B1,C1) one below other. If 4 is Input then 4 Columns (A1,B1,C1,D1) will come below each other so on and so forth.
View 6 Replies
View Related
Oct 24, 2008
.............................24............30............36
300014999..........9.00%.......11.00%.....12.00%
1500099999........9.50%.......11.50%.....12.50%
100000249999.....9.00%......11.00%.....13.00%
I need an all inclusive formula (all in one cell) that would look at the above table and depending upon the price paid (3000-14999 or 15000-99999 or 100000-249999) and depending upon what monthly term they choose (24, 30, or 36), the appropriate finance charge would be used to calculate a total cost (9-13%). The only way I know to do this is by using IF's and AND's, but there are simply too many arguments and I cannot properly write the formula.
View 4 Replies
View Related
Jan 31, 2009
Example: Table ...
View 9 Replies
View Related
Feb 21, 2009
Deciding to try and get to grips with Excel for basic accounting, I'd just like to check some things before I start filling columns... Say in column D I have a list of names, and in column E I have a list of figures: John Smith £250 Harry Davis £350 John Smith £500 What would be the formula for finding all occurrences of John Smith, and adding up John Smith's figures to give a total? In the simple case above, the answer would be £750. Would it matter if there are any empty/blank rows in the list?
View 9 Replies
View Related
Jun 17, 2014
Attached is a file containing stock codes and description. The stock code is repeated but i would like to have one line description per stock code.
View 4 Replies
View Related
Mar 13, 2009
I have a column with a description that includes color in the description, I want to sort by color with breaking up the description, is there a formula that will look at the description such as "Am Cannoe black tecan" and in the cel next to it come back with "black". Then I would be able to sort by color column, otherwise I would have to text to column and break up the description, etc,,, ect....
So column a1 has the description, I want a formula that will put in column b1 "black" I have 3 colors, so I can do one at a time or add to same formula.....
View 6 Replies
View Related
May 14, 2009
I am looking for a way to use the sum.if formula, combined with the find.spec (I am not sure if this is the correct formule, at the moment I only have the Dutch Excel version, it is meant to find a word as part of a cell)
The part I am struggling with is how to find the correct cells to "sum" when the criterium is part of the cell contents. e.g. Paul is the description in am looking for in "Paul is riding his bike" If so, then add the turnover to Paul. I have made a demo to clarify what I mean.
View 4 Replies
View Related
Dec 9, 2009
I need help to split text (shown below) into 3 columns, now all text is in one cell.
e.g.,
HIPP 1 PLUS PROBIOTIK 600 G MILCHNAHRUNG PULVER 1607000286
I have over 30,000 rows of data which contains different number of spaces within a cell. Please see attached file.
View 14 Replies
View Related
Jul 29, 2009
I have created a pull down menu but cannot figure out how to add descriptions with them. The items in the pull down menu are codes instaed of making people look up their definition in a printed out table,I am hoping to figure out how to make it so that as people scroll through the pull down menu a corresponding definition for the "code" listed appears.
View 9 Replies
View Related
Apr 8, 2008
I'm trying to find
1) the last and
2) first values in a time series of data in which the the data points are scattered all over the place
AND I'd also like to
3) get the corresponding year ( Heading name) for both the
3.1) last and the
3.2) first data point
I've found solutions for all but 3.2. Thus the question remains:
How could I lookup the heading description for the first value on a row?
example:
row #; lookup; year1; year2; year3; year4;
1; year3; ; ; 23; ;
2; year2; ; 21; ; 53;
3; year1; 12; ; 45; 2;
In case it helps anyone looking for same information or providing help to solve my problem 3.2 the other solutions I've found are:
1) lookup last value in row
i) Find Last Number In Row & Return Heading
=LOOKUP(9.99999999999999E+307,R5:AI5)
... also an additional VB solution
ii) Find Last Number In Row & Return Heading
=INDEX(M12:M500;MATCH(9.99999999999999E+307;M12:M500))
... page also includes solution for 2), below
iii) Find Last Number In Row & Return Heading
... 2 VB solutions
2) Lookup first value in row
Find Last Number In Row & Return Heading
=INDEX(M12:500,MATCH(TRUE,INDEX(M12:M500<>0,0,0),0))
... page also includes solution for 1), above
and
3) Lookup heading of last populated cell in row
Find Last Number In Row & Return Heading
=LOOKUP(2,1/(I9:CN9<>""),I$5:CN$5)
4) Could I e.g. modify the above to lookup the year of first value?
View 4 Replies
View Related
Feb 21, 2008
I have a list of tenants.
Column A is the building number
Column B is the Area in metres squared
Column C is the tenant name
What I want to do is sum the vacancy (in sqm) for each building.
Ie, look at column A and choose the rows relating to a particular building, whose number is in column D1
Then look just at those rows and select those tenants named as "vacant"
Then add the areas from column B of those vacant plots.
View 12 Replies
View Related
Feb 17, 2010
In basic terms I have column A containing a list of dates, starting from 01/01/2005 and increasing by 1 day for each row, In column B I have the value for the day. These dates and values are still being used so the number of rows will increase day by day. I would like a formula to tell me the average total for January. So it would need to SUM each January before giving the average. I realise I could prob do this with a pivot table but if someone could give me a hint for a formula that would be great.
View 2 Replies
View Related
Dec 10, 2013
I have a number in a cell, lets say its 900. I want to multiply this number by 12 and then divide that by 52.
So a calculator I would simply type in 900 x 12 / 52 = 207.7
On excel I tried:
=sum(a1*12) /12
and it didn't work....
View 2 Replies
View Related
Feb 12, 2009
I am trying to create a macro that will sum the total number of 1's 2's 3's 4's 5's '6s in a range of cells d17:100 and return the number of 1s to cell a3 and number of two's to cell a4 and number of 3s to cell a5 and so forth.
I also need this to run each time any changes to any cell on that particular worksheet is made - sort of like 'refresh all the sums' type of thing.
I have been working on this spreadsheet for weeks and can't get past this part!
View 9 Replies
View Related
Feb 25, 2009
I need a formula that will help me sum a row of numbers but, if at anytime there is a zero it should give me zero and the sums should start over at 1.
View 9 Replies
View Related
Jul 26, 2006
I made a budget for my project and have to include a macro. I wanted to have the macro pop up a box for the user to input the expense they wanted summed for the year (student loan, car payment, etc) and then have the macro sum the payments from each month. I couldn't figure out how to do the pop up box either, so figured I could just do multiple macros (20 or so) but then ran into the added difficulty of needing to only use one cell out of every four.
I have columns for budgeted, actual, monthly varience and year-to- date varience and only wanted to sum the actual.) I have read the thread about summing every nth, but trying it without the macro didn't give me the same answer as adding each on a calculator. Also, trying to record a macro of just adding cells didn't work (I figured it probably wouldn't, but I tried anyways.)
View 9 Replies
View Related
Feb 24, 2014
Any way to display the description of options (list) which in this case is a numbering code (like ECG201) in a data validation cell.
The length of the cell is not long enough for the users to know the appropriate description of the selected lists. I am now thinking there should be a way to display the description of each item so that users will know that ECG201 means "International College of Excellence", for example, instead of having to go from one worksheet (area) to another to know what an option means.
View 1 Replies
View Related
Aug 10, 2009
I often use pivot tables to summarize accounting data. I wish to summarize the data by account number, but also wish to display the account description next to each account number. Both the account number and account description are separate columns in my original table of data.
I've always managed to do this by the use of lookup formulas after the formation of the pivot table in a column outside the pivot table, but it would be preferable to have those descriptions as part of the table.
If I designate both the account number and account description as row labels, they land on two different lines.
View 7 Replies
View Related
Jul 3, 2014
I'm trying to map the cases present in the sheet 1 to Sheet 2. Here the sheet2 I have highlighted the rows yellow color that needs to be updated by using excel formulas.Here the sheet should be updated with the description below mentioned along with the formulas..Highlighted cells in the sheet2 is B,C,I,J,T,U. I have designed the below condition in the same order
B cells should be updated with the reference of Sheet1 with the below condition:
Identify the "(B Value)"Claim with below condition (D Value)
C cells should be updated with the reference of Sheet1 with the below condition:
Verify whether (I Value) Mapped to the below Coverage in CAS (K value)
C cells should be updated with the reference of Sheet1 with the below condition:
Verify whether the Incident (Q value)is below for the Coverage (K Value)
J cells should be updated with the reference of Sheet1 with the below condition:
Verify the the Exposure type(P) is below for the Coverage (K)
T cells should be updated with the reference of Sheet1 with the below condition:
Verify the cost created the reserve coverage (K value) is below (N value)
U cells should be updated with the reference of Sheet1 with the below condition:
verify the line category of the payment done on the coverage
(X value of all the conditions for Sheet1 value)
View 1 Replies
View Related
