The formula below contains an error (one of many as I've been trying to resove this). = COUNTA(IF(CZ4:DE4,DI4:DN4,DR4:DW4,EA4:EF4,EJ4:EO4>"1",Games / Activity,Games)) The formula should return the following result:
If two or more cells in the above ranges contain numbers and cell C4 isn't empty then return the words "Games / Activity", otherwise return "Games". If C4 is empty keep blank. Can this be done with a standard formula?
look at the formulas in column cells G28..G33 and tell me where I went wrong.I have attached the file.
View 2 Replies View RelatedMy setup is - excel 2003 sp3 / windows xp
On one sheet (Data) I have a list of action items, each with owner; target date; classification and in some cases revised target date. I'm trying to report on these fields and provide a status, by owner and classification, of how many are overdue; due this month; due next month; due beyond 2mths.
Using a SUM array formula on another sheet I can count the number that are overdue based on date; owner; classification; and target date, but can't find a solution if there is a revised target date. Any guidance you can give would be greatly received.
=SUM((Data!$L$3:$L$27=D$18)*(Data!$P$3:$P$27=$C23)*(Data!$N$3:$N$27
I have a transactional data set with a line for each transaction and I am looking to count the number of documents (each contains multiple transactions) against criteria.....
It looks something like this.....
Column A Column B
Document No Category
11000001 A
11000002 B
11000003 B
11000002 A
11000001 A
Is there anyway to do this without subtotalling for each document and then a count?
Column A has current building, column b has future building. Would like to count the number of changes without adding a separate column with an if statement.
View 3 Replies View RelatedIm having a problem finding a formula that will count and take an average of multipul ifs.
On sheet 1 of the attached file is multipul queries raised for us to answer.
On sheet 2 is an empty table which i would like help formulating.
In the Submission Lag Time Column i would like a forumla that looks down Column L on Sheet 1 and gives me an average for the amount of days a query has been with us from each company (column B) per month (going off the dates in column D).
I the same formula will be used for the other 3 columns, put looking down a different column on sheet 1.
I'm trying to find out if there is a way to count a specific number from a cell that has multiple numbers in the cell. I have attached a worksheet.
Example, on the attached worksheet, I want to count how many 16's in column C and input that total number into G18. Then I want G19 to tell me how many 17's in Column C, then how many 18's in G20 and so on. I want to do this for all days in a month, 1 through 31. If there are no numbers, then "0" or a blank cell is ok. Not to concerned with zero's.
Attached Files: xlsx Book2.xlsx‎
What I require : Column F to be blank as long as the following is true:
1) Column A (Order) cannot be blank.
2) Column E (Status) has to be equal to either "Completed" or "Successful".
3) Column D (Sales) has to have the same date as Column C (Contact), but due to bad data quality each order number can have several rows and as long as the Sale date from one of the other "Duplicate" rows equals the contact date it needs to be counted. This is as long as there is not already a row for the Order that already has Sales / Contact day matching.
In the attached spreadsheet, I have highlighted the rows that should be blank in column F in green. My attempts at formulas to count this are in the columns highlighted in red.
WORKAROUND.xls‎
I have multiple columns in excel that contains values like this
A B C D E F G H
TrxVolTrxVolTrxVol Trx Count Vol Count
122001400013500
125031290012499
130001300012700
Now at the last columns G & H i want to get the result that how many column of title "Trx" are having values greater than or equal to 1 and how many columns of title "Vol" are having values greater than 2500 respectively.....
My question is:
I do multiple audits using excel, and would like to start a database as I put more audits into one folder. So it updates the values as I insert more audits into the folder.
My audit is a workbook which has multiple worksheets. But for each audit, it is the same worksheet with the same cell. The audit consists of yes and no questions, where you put in a x for either one. I would like to start a database, so for each question on my audit, I would have a percentage of yes or no for all my audits. For instance, question 1 , 7 out of my 10 audits, I had yes for that question.
How do I write a macro, so it counts the x's for multiple workbooks, and updates automatically for each question as I add more audits into that folder.
I have a number of records that have in one cell a string of the form 1,2,3 etc (up to 10) representing conditions that have been met. There are a number of options available for producing reports on all of the other attributes in a record but now we wish to add this element as well.
The reports I can produce using VBA and the INSTR function, however on the summary page where, a total of all records matching available criteria is shown, depending upon drop down items being selected, I need to add this element to the equation as well. The existing conditions are counted using SUMPRODUCT and a combination of IF statements and work fine.
As there is an option to create a report for both AND and OR, e.g. if condition 1 and 2 and 3 apply include in the report, or if condition 1 or 2 or 3 apply include in the report
The conditions are selected using a check box and a drop down list to select the AND/OR condition.
I have been trying various combinations of database functions, countif, find and cannot get them to evaluate. I suspect that it may be beyond my reach to use a formula and I will need to use VBA with a Worksheet_Change macro to achieve what I want, or alternatively redesign the layout to store the conditions differently, however if anyone has any ideas for a formula I would much appreciate hearing from you.
A sample worksheet illustrates how the data is currently shown. The string in the record column is created using VBA and then inserted into the actual record.
I can't seem to get a formula to work. I am trying to count the number of times "EE" shows up in a range only if "CR" is listed in that row within another range. For simplicity Column A has a list with the following values scattered from A2 through A100: CR, LT, ST, DI. Column B has a list with the following values scattered from B2 through B100: EE, EF, EG.
I need to know how many EE's are listed in column B if CR is on the same line in column A.
Count unique records in Column B where.
1.)
... Column H >=A1 and <=A2
2.)
... Column H <>"" and Column I <>"expired" and <>"" and Column I >=A1 and <=A2
3.)
... Column H <>"" and Column I ="expired" and Column H+120 >=A1 and <=A2
4.)
... Column H <>"" and Column I >=A1 and <=A2 and Column J =""
A1 - user defined (start) Date 1
A2 - user defined (end) Date 2
Column B - 6 digit number (or blank)
Column H - Date 3 (or blank)
Column I - Date 4 (or "expired" or blank)
Column J - Date 5 (or blank)
Part #1/1/20091/5/20091/6/20091/7/20091/26/20091/27/20091/28/20091/29/20091/30/20092/2/20092/3/20092/4/20092/5/20092/6/20092/9/20092/10/20092/11/20092/12/20092/13/2009ABC --20372027-00013211321-1304-001232122500
I have some data on rows that are similar to what is above. Hyphens mean no activity, then there are "stock" amounts and then "0" means out of stock. I'd like to count the # of times that the stock changes to 0, by ranges...so 1/27/09 - 1/29/09 would be counted once -- as a range...the answer here would be 3 times that stock went to 0 - hyphens ignored..
I'm sure if's some count if function, or multiple ones, but not sure.
how to count a specific text in two different ranges eg a text "moby" ....want to count how many times it comes in ranges b4:b20 and in b50:b70
View 3 Replies View RelatedI am trying to figure out how to sum/count between ranges. The attached file has the data. Basically I have the list of salespeople, Various ID's, Units, and sales. I want to count/sum by salesperson between 2 numbers. In the example below, I'd want to know Jones sales between ID's 1 and 3. The attached file.....
View 2 Replies View RelatedI have a pivot table (sheet1)that has dates along the top(jan to dec 08 and will eventually include 2009) and codes down the left side.
The pivot will change on a daily basis, i.e. more codes will be added, as the new date comes up.
On sheet2 is my basis formula's that count or sum up on a monthly basis but with a fixed range, so if the pivot chnages the formala does not!!
Can someone help me with a formala that is dynamic enough to cope with this, i.e. maybe using the month to help with the calculation?
I have a set of data and I need to count the match of two ranges in a matrix, example:
ColumnA | Column B
Week | Component
1 | AAA
1 | AAA
1 | CCC
1 | DDD
1 | CCC
1 | CCC
1 | AAA
1 | AAA
2 | AAA
2 | BBB
2 | BBB
2 | AAA
2 | CCC
I want a matrix like this:
Week/Component| AAA | BBB | CCC | DDD
1: 4 | 0 | 3 | 1
2: 2 | 2 | 1 | 0
3:
etc
I have 12 components and 104 weeks which means that using DCOUNT doesn't feel like an option.
I would like something like:
FOR RANGE A1:B3000 COUNTIF 1 in column A AND AAA in Column B
Need a formula that will count the number of occurrences of that same number in three different ranges. The number that is to be compared to is in a cell that is a result of a formula.
Example:
Lowest number found in three ranges: 30 (Answer is in A1)
Question: how many times does that number appear in the three ranges?
Syntax:
=Countif(Range1,range2,range3=A1)
Has to equal the cell reference value (A1), not a plain number (like 30)
I want to count the number of cells with a “#n/a” in for a cell range which is non-continuous. For example my cell range is: “H5,J5,L5,N5,P5,R5,T5,V5". I’ve tried a few different things but I can’t get the function to work.
View 8 Replies View RelatedI have many columns of data, each containing numbers ranging from zero to 100. I am trying to determine the formula to count how many of these numbers fall into different defined ranges;
e.g. how many pieces of the data are numbers between 90-99, how many are numbers from 80-89, 70-79, 60-69, etc continuing on down.
I attached a sample workbook which may be clearer: there is a tab (titled "data") with 3 example columns of data & there is a tab (titled "counts") where I hope to get counts for each range to be generated by a formula rather than count them myself.
Not sure if the following is possible, but this would be even faster if each column didn't have to first be sorted ascending/descending for the formula to be effective (as I have hundreds of these columns).
a column contains 'amount'. The amount can be from 1-999999. Some cells also contains #N/A (thr' formula) & blank (thr' formula).
I want to get NUMBERS of 'amount' whose value is
1-10000
10001-20000
20001-30000
30001-40000
40001-50000
50001 and above.
I am struggling to come up with a formula that will look at one column of data to check for a condition (for example, it says 7. Implement) and then look at another column to see if a second condition is met (this one is a date ie Feb-14) and count those that meet both. The data I wish to check is on a different worksheet if that makes a difference...
View 1 Replies View RelatedIn my workbook, I want to count how many duplicate words in 2 ranges.
Eg:
Col A are more than 10,000 words and Col B are more than 10,000 words.
I want to count total duplicate words in these 2 ranges.
I am attempting to count records based on 3 criteria in 3 different ranges and am hitting a wall. My formula will be on a different worksheet as it is the summary sheet.
Worksheet "Cases"
Column E - Type
Column F - Month
Column G - Year
Worksheet "Recap"
Recap month will be input into Cell I5 eg. "6" for June
Recap year will be input into Cell K5 eg. "2009"
Cells N10-N15 is a list of Types
Summary Cell J10 will need to count if
1)worksheet Cases column E = worksheet Recap N10
2)worksheet Cases column F = worksheet Recap I5
3)worksheet Cases column G = worksheet Recap K5
I am trying to count the number of instances of dates that occur in a single column. For example, I want to be able to count the total # of dates that occur between Jun and July, then a different count of dates occurring between Aug and Dec (see example below). I have tried this formula: =SUMPRODUCT((E11:E149>=1/8/2008)*(E11:E149<=12/31/2009)) but it comes back with zero.
Column A
---------
1/8/2008
5/8/2008
8/18/2008
9/18/2008
I've got several worksheets that all have the exact same layout that a user will enter unique information in to each worksheet. Then I've got a final worksheet that I want to have a button that the user can click and when they do, it will look to each worksheet and do the exact same process for each worksheet as follows:
It first looks to see if the worksheet is visible. If it is, I want it to copy the range A5 to K5 down until it gets to the last non-blank cell in column C. The first non blank cell that will be referenced will be C7. Then I want it to paste this information into the range A5:K5 on the final sheet named Sheet8 with the same values and keep cell formatting such as width and height, font. If the worksheet is not visible, it skips the sheet.
I want it to do this for each visible worksheet, placing the next visible worksheet info under the previous visible worksheet info. My current code as shown doesn't do that. It requires that something be inSheet8 A6 before it will even paste, then it pastes the info from A5:K5 but it doesn't do just the values nor does it keep the formatting. What I mean about not doing just the values is some of the info that needs to be copied comes from a drop down they can choose from and it copies the actual drop down menu. Also, it seems to copy all of the ranges from each sheet and paste it into just A5:K5 on Sheet8 and overwrites each other instead of pasting Sheet2 just below the information from Sheet1. So the only information shown after the entire process is completed is the information from the last visible sheet.
If Worksheets("Sheet1").Visible = True Then
Sheets("Sheet1").Range(Sheets("Sheet1").Range("A5:K5"),
Sheets("Sheet1").Range("C7").End(xlDown)).Copy
Sheets("Sheet8").Range("A5").End(xlDown)
End If
[Code]...
I have a problem finding the correct formula for counting matches with conditions between 2 non-equal ranges in Excel. The sheet is a try at making a working schedule template a bit automated.
For Week 1 each cell in the H16:H25 has a drop-down list (originating from BD30:BD50) where a work position can be chosen. The fixed list in BD30:BD50 starts with “<<SELECT>>” which is the default choice for the cells in H16:H25, and then “HOLD” before continuing with various work position names. K16:K25 is shift number 1 on Monday, L16:25 is shift 2 on Monday, and so on until Shift number 6. Then the rest of the days of the week follow (each with 6 shifts). Monday through Sunday (with 6 shifts for each) ranges over K16:AZ25. In the cells in K16:AZ25 the following can be entered: “x” (work), “o”(off), “-“ (leave).
The issue is the formula in each of the K26:AZ26 cells which are to total each of the shift columns . I want to count all the “x” in each column, but ONLY if the positions chosen in H16:H25 matches one of the positions in the list in BD30:BD50. NOT if a cell in H16:H25 displays “<<SELECT>>” or “HOLD” (even if it has a “x” entered in one of the Shift cells).
For example: .....
I'm scrambling my head to put this together. Could somebody show me how this is done?
I have two rows of data that contains dates in the mmm-yy format. The first row is the start date of the activity and the second row is the end date of the activity. Thus, in each column i would have an activity with its start and end dates. If i want to total the number of months taking into consideration only unique months (counting over-lapping months just once), how do i do that? These dates could have breaks in between, too. Like if an activity ended Jul-08, the next could begin Dec-08.
I am able to count the months using the datedif function, is there a match or other function that i need to use?
I inherited a spreadsheet that had an userform where the user checked off which 'pages' he wanted to print. The Ok button routine used if statements to run a routine for each 'page.' Here's an example of the original code for one page:
Sub Button2_Click()
Sheet7.Activate
Run "HorizontalPrintStuff" 'generic landscape pagesetup
With ActiveSheet.PageSetup 'specific pageset settings
.RightFooter = " Construction Assumptions"
.PrintArea = "CONSTRUCTION" 'the named range to print
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1 'this changes depending upon the page selected
End With
ActiveSheet.PrintOut
End Sub
The problem was it printed each page as a separate print job; and if you print to adobe, you get serveral files, not one file. That and it took a long time to run.
So I tried a different tack. If the checkboxes has true, then the printarea is set to that named range. If there were more than one named range on a sheet to be printed, I consolidated them. I did this with a bunch of if statements - very cumbersome.
Sheet3.Activate
'Sheet3.ResetAllPageBreaks 'disabled due to errors
Run "HorizontalPrintStuff" 'generic landscape pagesetup
With ActiveSheet.PageSetup 'specific pageset settings
.PrintArea = "DEVBGTALL" 'the named range to print
.FitToPagesWide = 4 'this changes depending upon the
.FitToPagesTall = 1
End With
I haven't shown all the code cause it goes on for 12 sheets containing 16 different printareas.
My current muck ups are .....
1) it prints every printarea/named range on a given sheet (I took out all the if statements trying to debug everything.) Is there another conditional argument that allows for multiple 'trues'?
2) the pagebreaks in printarea/named ranges that are multiple pages (like a 48 month schedule) won't stay set. I've tried both VPageBreaks(3).Location:= and .VPageBreaks.Add Before:=
3) the Sheet1.select false argument is always adding a random sheet to the end of the print job. Don't know why.
I can do all this in a recorded macro, just not the selection userform. I've thought about copying to another sheet or hiding columns and rows then printing, but that seems just as cumbersome.
To recap, i want to print out, as one print job, multiple printareas from mulitple sheets, based upon checkbox selection on an userform.