I've got a forecast from a customer and need to summarize it with part number, quantity and date.
The spreadsheet is part no in column a due dates in row 1 values at the intersection of part no and due date and i don't want 0 quantity to report back.
my output needs to be partno, date due, quantity.
I have a row that contains a different time (hh:mm) in each column where each column represents a different date which is display in row 3. If there is no time the cell is populated with "N/A"
I want to count how many cells for a specified Month/year are not equal to "N/A". I have been able get each selction criteria to work but when I try merging numeric and non-numeric queries I lose it.
The following formula was able to give me the count of cells "N/A"
=COUNTIF('Master Data'!$UA59:$ALZ59, ">0")
and this gave me a count by Month/Year
=SUMPRODUCT(--(YEAR('Master Data'!$UA3:$ALZ3)=2012),--(MONTH('Master Data'!$UA3:$ALZ3)=1))
When I tried creating one COUNTIF I was unable to get it without an error as I needed the YEAR/MONTH functions.
I need to formula to count the number of cells that meet the criteria below,
Find the letters 'AT' in some part of the cell and a blank cell next to it...
Doing a different formula for each, doesn't bring out the correct figure.
Is there a way I can sum the result of a formula on a range of cells that meet a criteria? For example, I need to sum the difference of only the cells that are >46. (a1-46)+(b1-46)+(c1-46)...+(g1-46).
In this case the result I'm looking for is on row 2:
Is this possible to calculate in 1 cell only (h1)?
A
B
C
D
E
F
G
H
1
44.2
48.6
47.5
0.0
42.3
44.6
49.5
??
2
2.6
1.5
3.5
7.6
I would like to perform a search on only the worksheets listed in a worksheet titled table of contents. I would like to use multiple criteria for this search and send only the unique results to a worksheet titled results. Each worksheet listed in the table of contents has a cell address for each heading that I would like to extract data from the same column. The attached workbook example shows the data that I would like to collect when I search for cells that begin with "AB" and cells that begin with "CD". I collected this data by copy and pasting all the data from each worksheet into the results page and then applying filters and advanced filter to remove duplicates. This method does not work well for the original workbook as the data is quite extensive.
Create_List.xlsx
I am looking for a formula that either accepts the number in a cell if the numbers on two other cells are "approved". In other words, I have on cell A1 my value to be tested. On cells A2 and A3 I have two numbers. On cell A4 I'm looking for a formula that copies the number on A1 if A2 is bigger then 0.25 and A3 is smaller then 0.35. In not, there's no copy or a N/A appears.
View 3 Replies View RelatedSo I am trying to rearrange some cells on a report based of some criteria using an IF formula and getting Blank cells. Here's what I have:
LastRow = Range("A" & Rows.Count).End(xlUp)
With Range("P2:P" & LastRow)
Formula = "=IF($F2=""/FEDERAL EXCISE TAX"",$I2,"""")"
.Value = .Value
[Code].....
So I just want it to identify whats in a cell and populate a related value if the text is present. Do I need to change formatting to find Tex or something along those lines?
I want to put a formula in a spread sheet that if block f5 is equal to or higher than 20% of block b4 then block d3 * .0 if less than then * by .0078
View 14 Replies View RelatedI have built the following code which should look through a data sheet and then work out the instances where it meets the following criterias:
= "Client A"
= "First letter of surname is "a"
The code is as follows:
Public rowcn
Sub compare()
Dim rngTemp As Range
Dim intCounter As Integer
loop_col = "Client A"
data_sheet = "Data"
target_sheet = "Summary"
rowcn = 2
Do.............................
I need to count the number of occurances that meet two criteria i.e.,
Status needs to be UNSOLD
Size needs to be =>10.5 and also <12.5
Dress
Status
Size
1
UNSOLD
9
2
10.5
3
12.5
4
UNSOLD
12
5
15
I have a file with many intercompany transactions. There is a range of business unit numbers that if anyone one number is in both Column B "Bus Unit" and Column E "Affiliate" I want the row deleted.
Please see the attached, it is more clear I hope. The two yellow rows would be deleted.
The list of business units that I would want to never be on the same row is "4, 7, 41, 44, 46, 51"
I am looking for an Excel VBA code that can delete pair of rows that has certain criteria. I have included an example in the attachment and highlighted the rows that should be deleted. Since the values in each coloumn is going to change every day , I am looking for a dynamic code.
The objective is to:
To delete rows that has same code (columnd D), same basis (coloumn E), same Effective (coloumn H), value of TT (Column J) is either TI or TO and has offsetting Amounts (L) ie the sume becomes zero. Example pair of row 7 and row 8 as well as pair of row 12 and row 13 should be deleted.
TO and TI are actually transfer in and transfer out. Since the TI and TO for these rows make the amount zero for same code and same basis on the same effective date, I do not want to include this in the spreadsheet.
I have a list of items (TR Sets) from 1 to 96 in the range B4:B99, with values corresponding to each one (Acid Number) in the range C4:C99. I would like to make a separate list that names each TR Set (using numbers 1-96) that has an Acid Number > 0.1 . Is there a formula to do this?
View 13 Replies View RelatedI need a formula that will tell me if EITHER two cells = a text word. I've tried a few things and can't seem to get it to work!!!!
See in the example of my spreadsheet below: If A2 = FALSE or B2 = FALSE then D2 should display "Allowed" if either are TRUE D2 should display "Not Allowed"
Is this possible!?!?! I've tried way to many different formulas and am close to giving up..
A
B
C
D
1
Not Slow
50%
FY
Change
[Code] ........
I want this macro todo is to start at the top of columns A to J
and work down the column and colour any cells that meet the criteria.
Range(“A1”).Select
Do Until ActiveCell = ""
If ActiveCell > 0 Then
Selection.ActiveCell.Interior.ColorIndex = 5
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub
I want to sum all cells in column A that have one of several tags in an adjacent cell in column B.
- Some of the rows in column A are not tagged
- There are currently 3 tags that are valid in column B (I, S, R), but this could increase (or reduce) in the future, say to I, S, R, E
- The possible tags are contained in a named range, but don't have to be
- It's not possible to restructure the data because the columns are in a scratch sheet, where a variety of calculations are created on the fly in the column. I've attached an example, which might make it clear
- There are several (up to 10) sheets like this in the workbook with lots of different calculations that are then summarised in to some analysis.
- The overall objective of the workbook is to be able to;
a) include/ exclude individual calcs for any one column
b) include/exclude a country - solved
BP Formula.xlsxI'm not sure what formula to use or if Excel can do this. I want to use it to determine number of blood pressures in a certain range. So if I use column A for SBP (systolic BP) and column B for DBP (diastolic BP). I want a formula to tell me if column A is between 140-159 or column B is between 90-99. I can get that part, but what I'm having trouble with is it counting each row twice if both column A and B meet the criteria, whereas I want every row (person) to only be counted once if either column A OR column B meet the criteria. (See attachment)
View 5 Replies View RelatedI have a spreadsheet which contains data for crime incidents occurring in a month. I have created a worksheet which uses "IF" statements to pull in data that occurred within the last 24 hours only. I would like to create a report that can be printed which has a crime category and lists all the incidents for that category below that occurred during the day. The issue I am having is that all "IF" and "LOOKUP" statements are line for line and I want to be able to skip blanks or where it is not the category in the title.
View 4 Replies View RelatedI have come up with 5 countif formulas that work perfectly separately but need them to be combines into one big criteria.
=countif(PM[ProjectAge],">=200"
=countif(PM[Customer Name],"*" & purch & "*"
AND so on for each criteria. H
Column b in sample is conditionally formatted based on it's values. I want to also apply that same formatting to the person's name in the chart in D2:I9. For example, Jeff is in bottom 50% so cell B2 is shaded red with red text. I would like to apply that same red shade and red text to all the cells in my chart that say Jeff. Also, as example, all of the cells in my chart that say Kelsey would be formatted with green shade/green text and so on...
View 1 Replies View RelatedI 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 need to return the values in Column A IF any of the adjacent cells (columns) contain a number.
Sample data and expected Results...
Sheet2 Â ABCDEFGHIJK1DateData1Data2Â Data3Data4Â Data5Â ResultÂ
201/01/20132Â Â Â Â Â Â Â 01/01/2013Â 302/01/201311Â Â Â Â Â Â 02/01/2013Â 403/01/2013Â 1Â Â Â Â Â Â 03/01/2013Â 504/01/2013Â Â Â
13Â Â Â 04/01/2013Â 605/01/2013 06/01/2013Â 706/01/2013Â Â Â Â
1Â Â Â 08/01/2013Â 807/01/2013Â Â Â Â Â Â Â Â 11/01/2013Â 908/01/2013Â Â Â Â
1Â Â Â 12/01/2013Â 1009/01/2013Â 13/01/2013Â 1110/01/2013Â Â Â Â Â Â Â Â 15/01/2013Â 1211/01/2013Â Â Â Â Â Â 2Â Â Â 1312/01/2013Â Â Â Â Â Â
1Â Â Â 1413/01/2013Â 1Â 1Â Â Â Â Â Â 1514/01/2013Â Â Â Â Â Â Â Â Â Â 1615/01/2013Â Â Â 3Â Â Â Â Â Â 17
I cannot use VBA, Advanced Filter or a Helper column, but I could use one additional cell to hold a count, so, the solution can only be a formula.
Attached excel sheet below. Suggest a formula to get the count of items that fall in a specific data range ?
Count of items that specify a range criteria.xls‎
I am trying to use COUNTIF formula to count how many item in a column that meet certain criteria, say between 10 and 20...
=COUNTIF(G1:G100,"AND(>10,<20)")
I have a dataset which is dumped into my model every month. The Report tab is a "user form" whereby the user can select multiple search criteria. The dataset is then filtered according to the search criteria entered by the user.
Problem is, that if the user only selects 1 of 3 search criteria and the other 2 cells are left blank, the filter filters on these blank cells and doesn't retrieve any data. Any ideas how I can overcome this?
Sub PopulateReport()
Application.ScreenUpdating = False
Dim MyFilter1 As String
Dim MyFilter2 As String
Dim MyFilter3 As String
MyFilter1 = CStr(Sheets("Report").Range("C2").Value) ' convert cell value to string
MyFilter2 = CStr(Sheets("Report").Range("C4").Value)
MyFilter3 = CStr(Sheets("Report").Range("C6").Value)
Sheets("Waste").Select
Dim Rw As Long
Dim Rng As Range
Rw = Range("A65536").End(xlUp).Row
Set Rng = Range("A1:W" & Rw)
With Rng
.AutoFilter
.AutoFilter Field:=20, Criteria1:=MyFilter1
.AutoFilter Field:=2, Criteria1:=MyFilter2
.AutoFilter Field:=13, Criteria1:=MyFilter3
End With
how to display my excel worksheet using HTML maker as I've downloaded the software but have no idea how to use it!
I have a 7-column 'Task' list that I need to query, and extract (preferably to a separate sheet) only those tasks that start 'ON' or 'BEFORE' the queried Date (or date range), AND/OR end 'ON' or 'AFTER' that date.
Column 1 = Dates
Columns 2-4 = Task IDs (ID1, ID2, ID3 - must match as a group)
Column 5 = indicates either Start or End of Task ('S' or 'E')
(each Task has 2 such listings - a Start [s] and an End [E])
columns 6 & 7 = Misc. & Notes (unimportant as identifiers)
So, if a Task (identified collectively by col. 2-4) starts On or Before and ends On or After (col 5) the date (col 1) queried, then that Task should be included in the results list. The tricky thing is that a task that starts long before the queried date and/or ends long after the queried date needs to be included in the results list - therefore 'S' and 'E' (Start/End, col 5) and the 3 Task ID (col. 2-4) must be used along with the date for the query. And, if a task starts (or ends) on the queried date, then its counterpart (S/E) should also be included in the results list (if available).
Tasks List:
Date | Task-ID1 | Task-ID2 | Task-ID3 | S/E | Misc. | Notes
Jan 1 2008 | AA | def | XX | S | B-11 | notes
Jan 1 2008 | FF | xyz | ZZ | S | C-44 | notes
Jan 2 2008 | DD | def | YY | E | J-55 | notes
Jan 2 2008 | GG | abc | CC | S | C-22 | notes
Jan 2 2008 | BB | xyz | DD | S | M-33 | notes
Jan 3 2008 | AA | xyz | CC | S | S-77 | notes
Jan 3 2008 | BB | def | ZZ | E | A-99 | notes
Jan 4 2008 | GG | abc | CC | E | C-22 | notes
Jan 5 2008 | AA | def | XX | E | B-11 | notes
Jan 6 2008 | BB | xyz | FF | S | J-55 | notes
Jan 6 2008 | DD | abc | AA | S | A-99 | notes.............
I have a list of brands in one worksheet that are abbreviated (Brand 1 = "AB-"). In another Worksheet I have a list of products that start with various brand abbreviations (ex: AB-12345, BP-12345), and in another column on the worksheet I have codes that represent certain characteristics of that style ("1"=flat shot), "2"=shot on model, etc). Now what I want to do is count how many instances I have of products that begin with "AB-" and have a "1" in the other column so I can get a count of how many flat shots i have to do for that brand.
So far I am using the below code to get a TOTAL count of products that start with "AB-", but i cant figure out how to write it so that it checks additional criteria in another column.
=SUMPRODUCT(--(ISNUMBER(SEARCH(A3,(OFFSET('photo list'!G2:G5001,,,,))))))
A3 contains the text "AB-" and 'photo list'!G2:G5001 contains the list of products that may or may not contain the text string "AB-". Column S (not shown in this code) contains the codes for how to shoot. Hope someone can make sense of this and give me a hand.
I have a spreadsheet of actions/tasks. I have a column with the expected completion dates of each action and another column stating whether the action/task is "open" or "closed". I would like to highlight any actions that have gone past their expected completion date and are marked as still being "open". Obviously i dont want any row that are closed to be highlighted. I know the =TODAY()- B1>0 formula will highlight the dates cell that have expired but how do i extend this formula to what i require.
View 5 Replies View RelatedI currently have a report with so many large array formulas that it is virtually unusable. I want to use the DSUM formula in place of my array formulas but I am vexed in regards to creating a DSUM formula that I can put in the top-left cell of my report and then copy down to the bottom right side of my report. Currently, the array-formulas sum data from a large list when it meets two criteria -- one part of the data-record in the list must match the row label of the current row in the report and another part of the data-record in the list must match the column label in the current column of the report. I have found with the DSUM formula that you can use a "formula" criteria in place of a static criteria...the problem I am having is that the part of the formula that points to the data-set must be relative while the part that points to the formulas criteria needs to be static -- in order to be able to make one DSUM formula and copy it down and over I would need my formula criteria to have the opposite setup (with the data-set part static and the formula criteria as relative).
View 9 Replies View RelatedI have a table with 3 columns with an unkown number of rows (text, date, date) that is being imported daily.
I want to create a 4th column with dates starting from today and each subsequent row be one day earlier. I want to look at 30 previous days.
I then want to count the number of rows (looking at column 1-3) with the following criteria:
Countif column2<= "date in colum4" AND column3< "date in column4"