Multiply Cell In Column A If Cell In Column B Is Equal To 5?
Mar 25, 2014How can I multiply let's say A1 to 10 if B1 = 5
or multiply next cell like A2 to 15 if B2 = 10
ADVERTISEMENT
Multiply Every Nth Cell By Another Column Whith Merged Cells, THEN Find Column Total
Aug 6, 2009Please see the attached sheet. I have columns B through a lot (B through O in my oversimplified example). In every 7th row in each of these columns there is either a 1 or a blank/zero. I need to multiply that 7th number by the Quantity in column A, to achieve a total (ie the sum of each result of 7th cell*quantity) for each column in the bottom row, labeled "Totals".
In the actual version of my sheet, there are far too many rows to select everything manually. I've been fiddling with combinations of COUNTIF/COUNTA and OFFSET, but I haven't come up with a way to check for the 1 in every 7th row, THEN multiply that 1 by the quantity in column A, THEN add up the results for each column. As you can see, there are 1's elsewhere in the columns that are irrelevant to this particular calculation, so something like LOOKUP would also have to look in every 7th cell and couldn't just look at the column as a whole.
If you can't provide an immediate solution, but can at least point me to a resource that would allow me to devise a way to isolate every 7th row (THAT part is the sticking point), I'll surely post the solution to my own thread with updated keywords if I need it.
Macro To Make Bottom Cell In A Column Equal Top Cell
Feb 5, 2014I am trying to write a macro that will make the bottom cell in a column equal to the top cell. The top cell will not always be in the same row and there won't always be the same number of cells.
View 1 Replies View RelatedVBA - Delete Column If Cell Value Of Range Is Equal To Another Cell
Nov 5, 2013I would like to create a VBA code where it will delete the entire column if the cell value is equal to value in D2
For example:
Sub Delete_Columns()
Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("G2:S43"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = D2 _
Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireColumn.Delete
End Sub
======
But I think this line is wrong but I am not sure how to fix it - If (cell.Value) = D2 _
Sum Cell In Particular Row If Column Title Is Equal To Particular Value
Dec 23, 2013I am working on a proforma statement, REFER THE ATTACHMENT
A B C D E F G H I J Q
1 jun'14 jun'14 jun'14 jul'14 jul'14 jul'14 Aug'14 Aug'14 Aug'14
2 beg. Inv Purchas Unit Sold Beg. Inv Purchas Unit Sold Beg. Inv Purchas Unit Sold
3 foundation 4 10 11 3 15 13 5 16 18
4 Lipstick 4 10 11 3 15 13 5 16 18
5 Shimmer 4 10 11 3 15 13 5 16 18
6 Powders 4 10 11 3 15 13 5 16 18
7 Nail Lacquer 4 10 11 3 15 13 5 16 18
8 Eye Shadows 4 10 11 3 15 13 5 16 18
9
10
11 Total Purchase for june to aug 2014
12 Foundation ??
this is just an small sample of what i am working on i have got purchases, cogs and ending invenory for 2 years Products wise i want to add purchases for the particular product.
i.e. if for above data i want to find out the foundation purchased through june 2014 to aug 2014 what will be the formula
Excel 2000 - If A1 Equal Or Less Than 1000 Multiply By B1 Otherwise Multiply By C1
Oct 30, 2013Excel (2000), what formula would I use to accomplish the following?
If A1 equal or less than 1000 value, multiply by B1, otherwise multiply by C1.
Macro Code To Delete Entire Row If Cell In Column Is Equal To Value
May 7, 2014I need an easy code that searches all of column A and deletes the entire row if the cell has the value "-". It needs to find the last row of data using something like LastRow = Range("A" & Rows.Count).End(xlUp).Row
View 4 Replies View RelatedFind First Blank Cell In Column & Return Adjacent Date Less Than Or Equal To Today
Apr 4, 2008how to make the data look like a table with three columns. Other than the date, it is space delimited. I have a tracking spreadsheet where Column A is populated with dates for the year. Column C contains daily values.
I don't always start entering daily values on the first day of the year, e.g., this year the first value in Column C corresponds to March 9. All values in Column C are contiguous - there are no blank cells until the value in Column A is greater than today's date code. I would like to use a formula (rather than VBA) to look down Column C and find the first non-blank entry where the value in Column A is less than or equal to today(). In this case, the formula should return the value for March 9, 2008.
CREATE TABLES LIKE BELOW?Column A Column B Column C
March 1, 2008Saturday
March 2, 2008Sunday
March 3, 2008Monday
March 4, 2008Tuesday
March 5, 2008Wednesday ...................
Count Values Of Column If Column Header Is Equal To Name In Column A?
Apr 25, 2014For column "B" count all the 1's if column header is equal to name in column A.
For Column "C" needs to be checked if a value was filled in column "I" if yes then check if in column "L" has a value, count all these values.
Countif- Where Column A Is Equal To A Specific Number And Column B Is Equal To Another Number
Jun 29, 2008I'm trying to count if there are situations where column a is equal to a specific number and column b is equal to another number.
both columns are numbers. Does anybody have a good idea how to do this, maybe countif, but i am not sure how to do more than one item with that.
Match Names In Two Separate Column If Equal Then Pull Info In 3 Column?
Dec 6, 2013I need to look up the name in E2 in the list in column A and if it matches then lookup name in F2 in list column b, if it matches then the corresponding number in column C is displayed in column G. If neither names are in the 2 columns the words"Not on lists" is displayed in cell in column G.
The other problem is one name is spelled two different ways I want it to look for both spelling before moving on to looking up the second name.
I started with this formula but I'm getting #N/A or "not on list" when they are on the list. I'm using ranges prod_sum is columns AthruC, Last_name is range BthruC. =IF(AND(VLOOKUP(F4,prod_sum,3,0),VLOOKUP(I4,last_name,2,0)),"not on list")
Delete Duplicate Rows Where Column A And Column B Combined Are Equal
Dec 8, 2009I have a sheet where i want to delete duplicate rows where column A and column B combined are equal, i.e. range(Ax:Bx) where x is the current row. I am using the macro below but cant seem to get it working as I keep getting a type mismatch error and Im not sure why.
View 2 Replies View RelatedMultiply A Column And Result Appear In Next Column
Aug 31, 2010How do you multiply a column and have the results reappear in the nextcolumn? E3:E3631*1.3 = F3:F3631, is the code I tried.
View 3 Replies View RelatedLookup/Match: Compare A1 For The Values In Column B, Then Return The Corresponding Cell (column C) In Column D
Jan 31, 2008I've been searching the forums for this problem but I can't seem to find any answers. Anyway, this is the problem. See screenshot.
I want to compare A1 for the values in column B, then return the corresponding cell (column C) in column D.
e.g. D1 = 2, D2 = 1, D3 = 4, D4 = 5 and D5 = 3.
Count Column A If Value Of Column B Is YES And Column C Is Equal To 5
Apr 27, 2004=COUNTIF(A19:A28,(AND(B19:B28="YES",C19:C28=5)))
What is wrong with my formula above? I am trying to count Colunm A if the value of Column B is "YES" and Column C is equal to "5".
Multiply If Column Contains Certain Text?
Aug 8, 2014I have column A that declares the account as asset, liability, expense, etc. Some of the accounts need to be reversed. I'm having a hard time figuring out a formula or code I can use. Column C contains the values. I prefer VBA as I want to add it to the current macro I already have.
So if Column A has a liability, offset by (0,2), and multiply that value by -1. If Column A has an asset, leave the value as is.
I wanted to use an if-then statement but I'm not sure how to highlight entire columns. The number of liabilities, and assets, etc.
Count If A Cell Is Equal To Cell In Different Spreadsheet And Is Less Or Equal Zero
Apr 17, 2014How to correct my formula because it does not work?
Here the formula: =COUNTIFS(Data!C2:C24005,A17,Data!M2:M11149,"<=0")
I need to insert the formula in column C (Findings tab) which counts the rows in Column M (tab named "Data") that equal A17 (Column A in "Findings" tab) and which are less or equal zero. In addition if I drag the formula down I want to only the values be entered in highlighted in blue cells in column C (SKUs With Zero Sales in "Findings" tab) and empty cells in not highlighted cells. I attached images of these two tabs: Data and Findings.
Multiply Column - Final Formula For This Spreadsheet?
Jul 30, 2014I am having trouble with the final formula for this spreadsheet.
I want to be able to select "Holiday/Sunday" from the drop down men in column H, and have it multiply the total in column F by 2. There is already a formula in column F that sums column E-D.
Multiply 2 Values Based Upon Corresponding Column Cells
Oct 15, 2009I'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
................
Multiply Column Of Numbers By Fixed Number
Mar 1, 2008I did a search myself, then went through the 5 pages the "Post" function brought up. I'm sure it's been asked before, I must just not be using the right words
I need to tell every cell in one column to multiply itself by a firm number and a percentage. Ex: Column F will be the result of $77.80 times 75 percent all the way down the column. Column G will be $27.21 times 100 percent all the way down.
I could enter the formula into every cell, but I wondered if there's a way to just tell the whole column, only once, what it needs to do. PS-I work for an insurance company. The employees of a company want a spreadsheet showing exactly who pays exactly what. Ex: Their paystub shows $142.90 coming out of their paycheck. Their employer pays 100% of medical, 75% of dental. The columns would show Total, Employer Medical, Employer Dental, Employee Medical, Employee Dental.
Multiply Column Of Values By A Random Number Within Range?
Apr 20, 2012I have a column of values I need to multiply by a random number (percentage) within a range of numbers (15%-22%).
How can this be done?
Multiply Two Column Vectors Based On Reference Cells
Feb 9, 2013How do you multiply two column vectors D2:D6 and G2:G6 based on reference cells A3 and B3 that are equal to D1 and G1?
A
B
C
D
E
F
G
1
2
3
4
5
[Code] ......
Search Column E For Value - Copy Value In Corresponding Column A Cell To Another Cell
Aug 30, 2013search column "E" for a specific text value (let's say "it"). When found, copy the value in column "A" for the corresponding row where "it" was found to another cell "F2". When copying to cell "F2", if a value already exists in cell "F2", then no nothing else copy the value. If no "it" was found in the column, do nothing. I will continue to search, but since I am new to VBA it takes me a bit to find what I specifically need.
View 1 Replies View RelatedCopy A Column Cell If V Column Cell = YES Then Repeat
Jan 26, 2010Sub Printstuff()
Application.ScreenUpdating = False
For Each cell In Range("V168:V246")
If cell.Value = "YES" Then
'enter the name that is in The A (of the row with Yes) column in A2
'Run Sub Individual
'Print
'Run Sub View All
End If
'Next "Yes"
End Sub
Totaling Column B If Column A Is Equal To "Text"
Jan 4, 2010I was using the formula below and it returns a count of how many times a salesman is quoted. Now I am looking something very similar. This time though I am looking to total the price of the second column instead of just counting how many times there is something in the column.
SUMPRODUCT(--(Active!$D$4:$D$200=$A35),--(TEXT(Active!$C$4:$C$200,"mmm")=E$32))+SUMPRODUCT(--(Cold!$D$4:$D$200=$A35),--(TEXT(Cold!$C$4:$C$200,"mmm")=E$32))+SUMPRODUCT(--(Wins!$D$4:$D$200=$A35),--(TEXT(Wins!$C$4:$C$200,"mmm")=E$32))+SUMPRODUCT(--(Sold!$D$4:$D$200=$A35),--(TEXT(Sold!$C$4:$C$200,"mmm")=E$32))+SUMPRODUCT(--(Lost!$D$4:$D$200=$A35),--(TEXT(Lost!$C$4:$C$200,"mmm")=E$32))+SUMPRODUCT(--(Dead!$D$4:$D$200=$A35),--(TEXT(Dead!$C$4:$C$200,"mmm")=E$32))
Multiply From Data In Cell On Sheet2 From Cell On Sheet1
Sep 18, 2012I have a value in a cell in Sheet2 (in this case its price per ft), on sheet1 is where I would input the number of feet. So can I multiply from data in cell on Sheet2 from cell on Sheet1and if so....
View 6 Replies View RelatedFormula For A Cell Contains Specific Character Then Multiply By Another Cell
Feb 17, 2014Can I get a formula for the below specification.
if a cell contains character A THEN multiply by cell B * 0.02,if a cell contains character B THEN multiply by cell B * 0.05,if a cell contains character C THEN multiply by cell B * 0.010
Conditionally Format Each Group Of Rows And Each Column And Color The Cell In Each Column
Nov 27, 2009I've a worksheet that is 3750 rows of cells from A to DT and contains only numbers with the exception of a header row. The rows are in groups, primarily 4 rows, although there are exceptions - these rows are separated by blank rows. Small example as follows.
0:001:002:003:004:005:006:007:008:009:000000-296567-6-1-400000286567-300-230000715557-16-11-40-18141755226-348405-362203081417192125-893581061800-2443-11124-289326-81318-1105027854331331211318-10216-161235526785691450451115951-17-1882505656683093102671056-191-33-2710000554740971-61-78-34224107-169-128-17090179-85220-55-63-1-162-140-242-235328266319106113-214-64-148205-4108120-142076-167-60-68-6418081102-800000000000-3099695807185-80356138-123194186210677727125-23233092-2942944748055564476-422051-224-34461486649597117-84209-22-251-94
I have been trying to conditionally format each group of rows and each column and color the cell in each column of four (or less) numbers in each group that represents the maximum number.
It was easy enough to come up with the conditional formatting BUT it will take forever to do this manually. I've been told to use a macro but I know nothing of programming. Has anyone seen or heard of a macro that might already be written to perform this onerous task.
Formula To Make A Cell From Column A Meet Closest Value In Column B
May 11, 2014I've got a table of different values in column A.
I'd like the cell D2 ("order") to show up at column B next to the closest value of 35.00 of column A.
Consequently, how to make "order" meet the closest several value of 35.00?
I've attached a xlxs file with more detailed description.
Ex_closest_11_05.xlsx
Macro - If Cell In Column Is Blank Then Input Text Into Same Row In Another Column
Sep 19, 2008I am looking for a Macro that will search a column for blank cells, and when one is found will add text to the same row in another column. For example: The below is a spreadsheet. I am trying to find something that will search through column "C" in this case and add text (of my choice) to Column "A" if the cell is Blank. So Since cell C1 is Blank then Type "ERROR" is cell A1.
A B C D E
1 X X X X
2 X X X X
3 X X X X X
4 X X X X
5 X X X X
