Formula To Sum Values Based On 2 Cells
Mar 30, 2007
I have three columns of data. I want to give the average sales per person per year but I don't know how to combine two columns in the formula.
ManagerYearSales
Sarah2006200
Sally2006150
Stuart20063000
Steve20064000
Sarah2005100
Sally2005120
Stuart20054500
Steve20055000
I want the output to be something like:
Average Sales Per Year
20052006
Sarahxxxxxxxx
Sallyxxxxxxxx
Stuartxxxxxxxx
Stevexxxxxxxx
For example, I want to give 2006 sales for Sarah.
I know that I could add an extra column to my first table which concatenates the Manager and Year. HOWEVER, In my example it would be fine but in my sales report there is a lot more data with a lot more going on. I don't want to have additional hidden columns that people might stumble upon and change).
I want to use formula and NOT code as other people will be using the sheet and making changes to it. I want them to simply be able to autofill any additional rows rather than me having to update code.
I have attached my example.
View 9 Replies
ADVERTISEMENT
Jan 9, 2011
I looking for a macro that will go through multiple sheets & change specific cells to values if the column header is = to value set in specific cell.
for example
I would like the macro to look at row 3 in each tab (page 1, page 2, page 3) and if the value you is equal to X (parameter input on different sheet) then change the formula to a value in row 6 & row 12 of that column.
I'm attaching an simple example that i looking for this on. The green cells are the one i would like to change to a value.
Book1.xlsx
View 8 Replies
View Related
May 22, 2014
What I have In Column B, I have the datesIn Column I, I have engineers name What I need I want a macro to generate Serial Nos. (1,2,3....... n) in column A If an only if the date in column B is today's date and the engineer's name matches with the PC's username
The following is my code
[Code] ....
Above code runs without errors but does nothing.
View 4 Replies
View Related
Apr 25, 2006
I want to sum the values in cells E2:P110 based on the values column D. The
values in D are formulas resulting in something that appears to match D112 in
some cases. I'm using the following equation:
=SUMIF(D2:D110,D112,E2:P110)
My problem is that D2 :D10 have a formula in it and it's not matching. If
I enter the result of the formula, all is good. How should I deal with this?
View 11 Replies
View Related
Jan 6, 2008
I have the following data in 1 of the tabs.
[TABLE]
ClassSequenceAB
10SE132422
20SE23212
20SE321
20SE23425
10SE332455
15SE132412
10SE234
[/TABLE]
I want a unique count of sequences in a different for that class only if that particular row in 'A' or 'B' is populated. The result set should be as follows:
[TABLE]
ClassAB
1032
1511
2012
[/TABLE]
Can this be achieved through a formula?
View 8 Replies
View Related
Oct 25, 2008
I am working on a sheet which calculates payroll. I have a list of people in column A, and in column B, i have their different professionnal status. Their wages vary according to their professionnal status and are in another column C. How can i sum up the wages of only certain people, based on their common professionnal status in column B?
View 3 Replies
View Related
Mar 26, 2007
I need to highlight a cell when its value exceeds parameters based on the production line it comes from. If the line is K11, then i need this cell to highlight when its value is either < 0 or greater than 221. If the line is K21, than it needs to highlight when its value is <0 or greater than 474.
View 9 Replies
View Related
Aug 5, 2014
I am trying to create a formula that will do the following:
If A1 is between 95 and 99 then populate with 2
If A1 is between 90 and 94 then populate with 3
If A1 is between 85 and 89 then populate with 4
If A1 is between 80 and 84 then populate with 5
If A1 is below 80 then populate with 6
View 11 Replies
View Related
Feb 26, 2013
How do I make a formula based on two incremental values
e.g I drag this down
=ROWS(A$1:$A1)*B$4
where B4 is say 0.2
I will get
0.2
0.4
0.6 etc
But what if I want to start from 5 so it will be like this
5.2
5.4
5.6 etc
View 2 Replies
View Related
Oct 3, 2009
i need a formula that will sum values in Col H based on the criteria set in CELL B1. The tricky part is that i need the summed values to be converted using the FX rates in Cell M:N, i have provided an answer on what the formula should return in CELL C2....
View 9 Replies
View Related
Oct 12, 2009
1. In this formula in column C, below, i have A2 as the look up... sometimes this will be A3 , A7 or other... how can i vary this formula? I've tried it all ways.
Range("C2:C" & LR).SpecialCells(4).Formula = "=VLOOKUP(A2,Data!$A:$B,2)"
It need to be inserted based on the first cell in column A which has a value.
2. after this has done its thing... how can i paste the values to ONLY those cells which have been affected by the formula. I don't want to copy, paste special the whole column as there are sub total and other exciting things included.
View 9 Replies
View Related
Jun 6, 2007
I have three variables; Independent variables x & y, and dependent variable z. x & y can take the values 1,2,3 and can equal each other z can also take on values 1,2,3 but can never equal x or y. I tried using if/and statements to solve this (see below), but I would have to do it for each case and it exceeds Excels embedded function limit.
=If(and(B7=1,B10=1),riskduniform(E4:E5),if(and(B7=1,B10=2),3,if(and(B7=1,B10=3),2,if(and(B7=2,B10=2),riskduniform(1,3),if(and(B7=2,B10=1),3,if(and(B7=2,B10=1),3,if(and(B7=3,B10=3),riskduniform(1,2),if(and(B7=3,B10=1),2,if(and(B7=3,B10=2),1)))))))))
View 3 Replies
View Related
Jan 20, 2014
I would like to lookup and return results based on the values of two cells A1 and B1.
The returned results are from column G to column I and always returned in the same location of the spread sheet.
For example if P2560 is selected in A1 and D10 in cell B1 then after lookup the results would be the values of G3 to I4. Alternatively if A1 is P2560 and B1 is D20, then the results are the values of E5 to I7 and so on.
see the attached spreadsheet for more details. This shows the main data (A2 to I15) and the expected results depending what value is selected for cells A1 and B1.
View 5 Replies
View Related
Apr 18, 2014
I'm using the below macros to hide or unhide rows based off of a value in column A. Column A contains an If statement that looks for a value in column F, and depending on whether the corresponding cell in column F contains a value the if statement returns either True or NO-IF(F>0,True,"No).
It currently takes quite a bit of time to run this macro. Given the specs on this machine, I'm convinced that there is probably some loop or inefficiency in my code.
Sub Button1_Click()
Dim cell As Range
For Each cell In Range("a59:A1472")
[Code]....
View 3 Replies
View Related
Sep 12, 2005
I have a column say column A. It has a pattern of cells where there is a code C10A3(alpha numeric) in say A1 and then in cells A2-A9 there are descriptions (alpha only). This continues uniformally down to cell 300. So every x amt of cells down a code appears and then below this for x cells is a description. What i would like to do is create a macro to concatenate the code waith each of the descriptions and paste it in the adjacent cell to the description. So....
A1 =C10A3
A2 =Global
A3 =America
A4 =South AMERICA
A5 =aFRICA
ETC
A10 =D05a9
A11 =Global
I would like to concatenate A1 and A2 and have the output in B2 (C10A3Global.)
And A1 and A3 with the output in B3 etc. This should continues until A10 (where the cell contains alpha numeric characters) where nothing is entered in B10 and then in B11 there should be D05A9Global....
The only difference between the description and the cell is numeric characters...
View 9 Replies
View Related
Jan 31, 2014
How to do this:
I have many sheets called;
Input Jan
Input Feb
Input ....
Meaning 1 for each month.
I have another sheet where I want a formula that can
..............................................JUL
CapEx.................................... EBITDA...............................EBITDA %
"=('Input Jul 2014'!$BI$3)/1000"
The "Jul" which stand for July, it is possible to have a formula where I can have certain text in a formula that is based on the value in B1 (In this case, B1 being JUL in bold writing.)
Like this;
=('Input &B1 2014'!$BI$3)/1000
Or must I manually go in and replace all formulas for each month?
View 4 Replies
View Related
Nov 29, 2013
I have attached an example workbook with a tiny subset of data and a number of criteria from the Dashboard Engine page removed.
What I need is to sum the total value for each division on the second sheet based on the date criteria (so for the first cell Jan 1/2010 - Jan 31/2010) and on a match between division name on the Dashboard Engine sheet table and the Masterdata sheet. I have tried a number of variations but keep getting a 0 for each return.
Sample Workbook.xlsx
View 12 Replies
View Related
Aug 18, 2009
In this file i did paste special values so the links are no longer there, but in my original file alot of the values you see in the cells are pulled from other worksheets ( thats just so you know to lookin values of the cells and not the formula ).
I basically need row 18 starting from Column O to self populate.
Its based off of which has 1's in the Column. I have the completed here manually just to show you what i am looking for . I usually have to go to row 3 and Enter the data manually depending on which 1's are filled in for the column.
If i could have a method of having row 18 starting with Column O to self populate based off those 1's
View 8 Replies
View Related
Apr 2, 2013
I would like a formula to add up values in Col E based on a range of account numbers in Col C
For eg adding up Sales values in Col E based on Account number being series 3000-3015 in Col C (this particular series must exclude numbers ending in an alpha). I also have account numbers ending in an Alpha where I need to add up the values pertaining to these for eg 3000D-3015D, 3000K-3015K etc.
View 9 Replies
View Related
Sep 27, 2006
I have a spreadsheet that give me the percentage difference of two cell say a1 and a2 (=a2/a1)[format as % two decimal places]. The result is on say b5 as a %[format as % two decimal places]. Now on B6 I want to do this:
if B5 is >3.01% then b6 =" Market Test Required"
if B5 is +3% then b6 =110
if B5 is +2% then b6 =106
if B5 is +1% then b6 =103
if B5 is 0% then b6 =100
if B5 is -1% then b6 =96
if B5 is -2% then b6 =93
if B5 is -3% then b6 =90
if B5 is >-3.01% then b6 ="Market Test Required"
So on and so on.... I would really appreciate your help on this issue.
I have used excel for some time now but not with complex formulas or any vb.
View 9 Replies
View Related
Oct 15, 2009
I have one cell (B10) that will contain either "high", "medium", or "low". Based on one of these 3 values, I'd like to shade cell J15 a color with some white text. I got 1 of the 3 keywords working, not sure how to add in the other 2.
View 2 Replies
View Related
Mar 19, 2014
Now I know that merging cells is usually not a good thing in VBA, but I'm working on a data report that just looks bad unless longer pieces of text can span several cells to minimize column width.
So here's the problem:
I have a column of 1's and 0's in columns E and F, and text in Column G. I want to merge the cells in columns G and H for that specific row if there is a 1 in either the E or F column for that specific row. And I need to do this for a long range...rows 7-5000. Any ways that VBA can do this?
View 4 Replies
View Related
Jul 9, 2008
Based on a Beginning Date Value and an Ending Date Value - I would like to color a range of Cells - the month (dates) are in the header row.
For example: Beginning Date = 01/01/09
Ending Date = 06/01/09
I want the range of cells in the same row colored orange under Jan '09 to June '09.
View 8 Replies
View Related
Dec 21, 2009
I am looking for a way for Excel to fill in cells in a column that has data sporadically in it. I am importing a text file into excel and saving it as an Excel file.
In the example attached, I need the numbers filled in all the way down to the next number. I don't care about the text, just the numbers. I realize I can copy and paste, but these files can be several hundred rows long and it seems there should be a better way.
View 4 Replies
View Related
Nov 30, 2012
I am trying to show a value in a cell, based on the values found in other cells. Essentially, here is what I've got:
If C2 is greater than A2, B2, D2, then put the value found in C1 in E1.
View 9 Replies
View Related
Apr 15, 2014
I have two columns, A and B (I technically have a lot more in my sheet but to make it simple just focus on these two columns). The two columns are Company Name and then Company Color. I want to write a macro that based on what color is in Company Code (B1:B500) to highlight Company Name (Column A) based on those values in column B.
Below is an example:
I would want GE to be Red for example.
Company Name
Company Color
Google
White
GE
[Code]...
View 3 Replies
View Related
May 6, 2009
Using Excel 2002. Here's my problem.
Column A contains the month (as text)
Column C contains an employee name.
Column O contains a reason for absence.
Column K is the number of hours of absence.
The employee's name may appear several times in the worksheet. What I want to do is count the number of hours per type of absence.
E.g. If A=MAY and C=BOB and O=SICK then total hours from all instance of K = X.
This will be used on a seperate worksheet where the name C will be referenced from a validation list.
View 9 Replies
View Related
Oct 15, 2009
I'm absolutely new to Excel and I've been trying to workout a formula to to test two conditions if true and derive the data from a 3rd column. In my attached excel file there's a Grade (A) , Status (B) and Mid Point (C) Tables. I'd like it to be formulated in a sense that forexample:
IF Grade= 1 AND Status= Q4 RETURN =3750*0.25
IF Grade= 1 AND Status= Q3 RETURN =3750*0.5
IF Grade= 1 AND Status= Q3 RETURN =3750*0.75
IF Grade= 1 AND Status= whatever (I mean whatever I input in this cell then it would retrun 3750 ofcourse satisfying the condition grade= 1) RETURN =3750
IF Grade= 2 AND Status= Q4 RETURN =5500*0.25
IF Grade= 2 AND Status= Q3 RETURN =5500*0.5
IF Grade= 2 AND Status= Q2 RETURN =5500*0.75
IF Grade= 2 AND Status= whatever (I mean whatever I input in this cell then it would retrun 5500 ofcourse satisfying the condition grade= 2) RETURN =5500
................
View 7 Replies
View Related
Feb 22, 2008
create formula/condition to colour fill different cells based on multiple Criteria.
View 3 Replies
View Related
Dec 19, 2013
I can manage an array formula that returns a value based on a criteria. Simple. But I want to add in an additional couple of criteria. Now I'm stuck....
My sheet looks at a manually entered postcode, finds out what region this is in, and returns a list of postcode I have defined as being in that region. (So the postcode WF1 3JY would return a region of Yorkshire, and list postcodes of WF, BD, L, etc)
I also have a list of engineers, with a column for their home postcode.I want to be able to list all the engineers from my list whose home postcode matches any of the values on the already created list from the postcode and region entered. So far I have this, which finds me all the engineers for just one postcode area.
View 4 Replies
View Related
