We were so close!. But it appears that the assumed correct answer only works if there are no repeating N. The repeating N gets the same count as the last Y and it throws off the sum ....
View 14 Replies
I am trying to come up with a simple formula to count a single cell if it contains either a 2 or 3.
The cell can contain numbers ranging from 0-8.
Even better would be if I could some how evaluate single cells based on the contents and then count the number of cells where the criteria for contents is true. The problem is the criteria differs from cell to cell (i.e. D2 could = 2 or 3, but E2 needs to be counted only if it contains a 4 or 5).
I know COUNTIFS only evaluate a range but it would be perfect if I could somehow get it to work for single cells. COUNTIFS(D2,2,D2,3,E2,3,E2,4,F2,2)
In the picture below, I need a formula in column E to count the unique occurrences in column A (excluding blanks) if its corresponding value in column B (B1 value) matches that in column D (B2 value). Currently column E is showing the values I would want the formula to return.
Capture.PNG
At the moment I have a formula as below:
=SUMPRODUCT(--(B:B=$D2),--(A:A<>""))
this will do a countif in column A if column B matches the value in column D, but would not weed out duplicates for me.
Modifying my formula. I have attached the sample workbook below.
Book1.xlsx
I´m trying to do a macro that changes the color of all fonts in a row if the cell in the column "R" has the date lower than a specific one, something like this:
Dim Data2 As Date
Data2 = Sheets("Sheet1").Range("today")
Range("R12").Select
Do While ActiveCell <> ""
If ActiveCell < Data2 Then
ActiveCell.EntireRow.Select
Selection.Font.ColorIndex = 3
Else
ActiveCell.Offset(1, 0).Activate
End If
Loop
But it just don´t work. The macro does that in the first row, than it stops. Do you have another code for this, or another way to do?
I have a formula that looks at a cell on a front sheet, and then returns the contents of that cell as the result if it meets the criteria. So for example this formula would be in Cell A1 on Sheet2 IF(SHEET1!A1,"New",Sheet1!A1,"-")
This formula is always in the same cell (different sheet) as the cell that it is looking at, down 1500 rows. Instead of having the formula named for each cell is there anyway to ask excel to 'look at this cell but on this other sheet'.
e.g IF(Sheet1!"This Cell" etc). That way no matter what cell you put the formula in it is always referencing the correct cell for the formula?
As per attachment, see data : 2604 Stock.xlsx
On sheet 1 column A and B. On sheets 2 I would like to make a seach tab - so if the people type part of the word or code it must return all the data from sheet 1 to sheet 2 that meets that criteria in full or part thereof. I have tried but can't get it to work, might it be that it is on 2(two) sheets ?
I do not understand code so I really do not know what to do where. Basically it should work as a Ctrl-F (find-all) function.
I have a worksheet (Data) that lists when pupils are in for Nursery sessions during the week. If they are in they have a 3 (hours) by their name in the relevant columns.
In the AM worksheet I now need to pull through a "register" so under each daily heading I need to pull through everyone that has a 3 next to their name under Monday AM / Tuesday AM / Wednesday AM etc. from the Data sheet. However, I don't want it to copy any blank cells. I then need to do the same for the PM sheet.
i am trying to get my excel formula to say if 2 date ranges in my excel workbook are the same (in seperate worksheets) to put a value from a certain cell into another cell in another worksheet. This is all in the same excel application...
The first step i have taken are:
1) if the 2 date ranges are the same to say 'true' if not 'false'
I am trying to get a forumula to say "if it is true to put a value from a different cell into another cell (both in different worksheets) but the same excel application...? but if its false to move on to the next date
I'm using the following on the Worksheet_Activate event to update rows with the date, (Cell AD1 contains the current date):
Code:
Range("X2:X2000").Formula = "=IF(IF(ISBLANK(W2),,$AD$1)=0,"""",IF(ISBLANK(W2),,$AD$1))"
Is there a way I can amend it so it only updates those cells which currently have no data?
At the moment it is changing every cell with the date which overwrites existing dates - I want it to update the new stuff only.
Say I have a column of cells filled with numbers that are ascending. What I would like to do is to pick out the first cell that meets my condition. For instance, these cells contain irrational numbers that increase from 0 to 100. I would like a formula to pick out the first cell that exceeds 10.2, and to return the row number of that cell.
In the case of descending numbers, if I would like to pick out the first cell that goes below 10.2, would the formula be the same?
I want to scan all sheets in a workbook and to delete a first row where a value in cell(1,1) is "table".
Sub DeleteFirstRowInWorksheet()
Dim SheetName As Worksheet
Dim i As Integer
For Each SheetName In Sheets
If Range("A1") = "table" Then
Rows("1:1").Select
Selection.Delete Shift:=xlUp
End If
Next SheetName
End Sub
It delete only in an active sheet. What's wrong?
I'm attempting to clear the contents from a range of cells on rows where a cell string may equal R, X, XX, Y, Z, ZX, #N/A.
The macro runs fine until it gets to a cell that contains #N/A. How to get this to work?
Sub Recalculate()
Dim r As Integer
r = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For Row = 13 To r
[Code] ........
I am trying to populate a cell based on which number meets the criteria I define. This is based on sales revenue, so if the revenue is less than $6.5M, I want to use a certain value. If the value is equal to $6.5M but less than $8M I want to use another value and finally if the revenue is greater than $8M i want to use another value. Here's my formula, but it returns $0.
=IF(B2<6500000,Bonuses!J45)*OR((B2>6500000)*AND(B2<8000000),Bonuses!F45)*OR(B2>=8000000,Bonuses!B45)
I need to add nonblank cells (cells have text) in a column that equal the
criteria of another cell.
So I want to add the cells in Column C that have text but also equal the date in Column A (which is equal to the date in Cell A1).
I have tried the follwoing:
=SUM(IF(A2:A19=A1,IF(C3:C19="x",1,0)))
=COUNT(IF((A2:A19=A1),C2:C19))
=IF((A2:A19)=A1,COUNTIF(C2:C19,"x"))
I have 2 sheets, sheet1 and sheet2. On sheet1 I have a cell (w,3) which displays either "OK" or "Needed". It will be like this for every row on the sheet. On sheet2 I have more information. I have been trying to create a macro or VB function that everytime the workbook is opened will scan the entire column W for any that say "needed" and if/when one is found copy the data in the cell in column A of the same row from sheet1 to column A of the first blank row in sheet2.
So say cell w,6 displays "needed", I then want to have cell a,6 copied from sheet1 to sheet2 into the first column A that is blank. I don't really care if when w,6 changes to "OK" it removes the entry on sheet2 or not, in fact if it does that would be great.
Here is the code that seems to be not so complicated and has copied information from one sheet to another, can't get it to work consistantly. I copied it off a similar post on this site and am not sure what the last value = "1" is doing but I doubt its helping my cause. The cell I need to trigger the action is in the 23 column, the cell I need copied is always in the first and the cell I need it to be copied into on the 2nd sheet is always in the first column as well. I'm pretty good with regular logical statements but I feel that a lack of knowledge about all the available commands is really whats hurting me on this....
Two Sheets:
1) Top Sheet
2) IRA
- Cells A4:A10 of sheet "Top Sheet" contain dates. i want B4:B10 to show the IRA value as of the date in column A:
____A__________|_B______________________
4 | 09/22/2005 | $ Total as of date in A4
5 | 10/06/2005 | $Total as of date in A5
6 | 10/20/2005 | $Total as of date in A6
7 | 11/03/2005 | $Total as of date in A7
8 | 11/17/2005 | $Total as of date in A8
9 | 12/01/2005 | $Total as of date in A9
10| 12/15/2005 | $Total as of date in A10
- Cells A6:A12 of sheet 'IRA' also contain dates but not in the same sequence (some date ranges had several transactions), B6:B12 of sheet 'IRA' contains the transaction values during the date range:
____A__________|_B______________________
6 | 09/22/2005 | $100
7 | _ _ _ _ _ _| $200.................................
I am having problems getting a formula to calculate the max if it meets 2 criteria. MAX of time listed by 1/past a certain date. 2/are of a specific client.
Here is my formula...
{=MAX(IF(All_Tickets_07012014!$D$2:$D$155685>1/1/2014,IF(All_Tickets_07012014!$O$2:$O$155685=$A2,All_Tickets_07012014!$G$2:$G$155685)))}
The data...
$D$2:$D$155685 - is the list of dates per ticket.
$O$2:$O$155685=$A2 - Are a variety of clients, where $A2 is a name of one of the clients.
$G$2:$G$155685 - Being a range of time stamps.
The formula runs, however it only shows the MAX of all entries, not within the specific date range...
Here is what I have. 4 Worksheets. The first worksheet is a summary page. I have 350 personnel that are broken down into three different groups. So each group has it's own sheet. Here is what I need to accomplish. Results need to be posted on the summary sheet.
I need to compare cells B2 & D3 for each row on a worksheet and display the number of times they match on a worksheet. For example how many times does EP & EP match on a certain row. I need to compare cells B2 & D3 for each row on a worksheeet and display the number of times they don't match on a worksheet. For example how many times does EP & MP occur. I've attached an example for reference
In theory I know what I should do, but I don't know the syntax. So here it is:
There are 1450 unique records in my XLS, every record contains 45 different rows(these are the phases) with their position(1....45). Every row has a status (Not Started, In Progress, Complete)
COLA(uniqueid)....COLi(it is a number, it is the position).....COLN(status)
id1....1......status
id1....2......status
id1....3......status
.
. .
. .
id1 ....45.....status
id2.....1.....status
.
.
.id(n)
basically I would like to get the last "in progress", If not found, the last "Complete, If not found then the first "Not Started". and put a "Y" right next to the row to a new column for all the groups(45 rows)
s/s has 325501 rows. Column C contains names of people (whether present or not -I enclose small attachment to illustrate).
Column J contains scores (if present). I need column N to list the last row number where each column C name scored points (not just when heshe was last present). I think I need macros which I can fill down both columns (??).
In the attached Exel work book I have work sheets named
Material Usage – Usage of materials
Estimate - calculate the Total material cost for a job
Material Cost – defines the material cost for each material type
In “Estimate” worksheet Job number is repeated but sub jobs falling under a particular job number is unique. Materials used for each sub job is different.
Once the job number is selected from the list box , I need to calculate the total material cost for each job. I tried sumif function but I don’t know how to get it to look up for each material type and get the sum .
ABCDE1DATEEMP1Days Between SalesEMP2Days Between Sales
23/6/2012 YES0NOÂ 33/5/2012NOÂ NOÂ 43/4/2012NOÂ NOÂ 53/3/2012 YES2NOÂ 63/2/2012NOÂ NOÂ 7 3/1/2012 YES1YES682/29/2012 YES0NOÂ 92/28/2012NOÂ NOÂ 102/27/2012 YES1NOÂ 112/26/2012 YES0NOÂ 122/25/2012NOÂ YES4
I believe I need a loop code to do what I need, because none of the functions I've tried have worked. I want to start at B2 and go down the column until I come to a YES. When I find a YES, I want to know the number of NOs that preceded it. Then I want to go from that YES(#1) to the next YES(#2) and count the number of NOs between YES(#1) and YES(#2) and so forth, until I run out of rows. For example, in C5, the answer is 2, because there are 2 NOs between YES#1 and YES#2 in coulmn B, and a 1 in C7, because there is 1 NO between YES(#2) and YES(#3) in column B.
here is a sample of the data
150
21
352
422
53
69
755
852
1224
13268
119
242
30
444
543
67.................
I know if I use dmax for only where first column equals 13 I get 460 but how do I get the second highest value for only those rows that have 13 in the first column (expect the answer to be 268). Then I want to do the same for 3rd, 4th highest etc.
I know large does it for one column and not only when the first column matches a designated criteria.
s/s has 325501 rows.
Column C contains names of people (whether present or not -I enclose small attachment to illustrate).
Column J contains scores (when present).
I need column K to list last row where column C name scored and column L to list last score.
I think I need macros which I can fill down both columns (??).
I want to create a sumif formula that will sum the data if it meets five different criteria. I tied to do an “Or” statement in the formula, but it doesn’t work. For example, I want to sum all the rows that contain: Apples, Bananas, Cherries, Pears, and Plums. How do I write the sumif formula so that it will do this?
View 9 Replies View RelatedI have attached a copy of a spreadsheet that I am putting together, What I need to achieve is, when a date is entered into the completion date cell the daily count down in the days left cell stops but still shows the value in that cell. The Annual or Monthly cal tabs are the ones i need the formula for.
View 5 Replies View RelatedI have gotten a code. It goes into Sheet 1 and picks up all the Rows (entire row) in which Column A = 1.
Instead of copying the entire row, I need to just copy the row but only specific columns (i.e. Row 2, Column B,C,D,E,G).
Is there a way to specify where it pastes values in Sheet 2 (say from column D) because Column A-C have pre-filled data which do not change. And when it pastes, it has no gaps (despite a gap in the copied columns (i.e. Column F in sheet 1 was skipped)).
VB:
Sub cond_copy()
'assuming the data is in sheet1
Sheets("Sheet1").Select
Sheet2.Range("A2:G5000").ClearContents
[Code] .....
IN column J(on sheet 1) i want it to return text (OB) if Sheet 1 column A1 equals Sheet2 Columns A1:A500. And if Sheet 1 column A1 do not equal Sheet2 Columns A1:A500 return text(IB).
View 1 Replies View RelatedI am trying to add the tuition to be reimbursed if the claim date is on or after a certain date and then deducting the total from a set amount. I have included a simple example. I tried a formula from the pierson examples but Im doing something wrong.
View 4 Replies View RelatedIs it posssible to use the xirr function with conditions. For example, an array formula that will only calculate xirr if a column meets a certain criteria or if the dates are within a range?
View 9 Replies View Related
