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 RepliesI 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 have a spreadsheet that contains two worksheets called: MasterApplication and LocalAdmin. The Local Admin worksheet as shown below contains the following information starting on row A1:D456 ....
View 9 Replies View RelatedI m trying to evaluate part of a string to determine if a number and if 6 char's in length. If Yes, return the 6 digit number, else 0
My formula below returns a #VALUE! error
It worked before I added the 6 char length condition, but sometimes return values of greater length that I do not need.
=IF(AND(ISNUMBER(VALUE(LEFT(C1809, FIND(" ",C1809)-1))),LEN(VALUE(LEFT(C1809,FIND(" ",C1809)-1)))=6),VALUE(LEFT(C1809,FIND(" ",C1809)-1)),0)
I am trying to perform a count statement in VBA as below, with the 1st Evaluate I get the correct response, however with the second option it shows a 0. The variable is obtaining the correct values as per the example of 6017Complete, this is proven with the MsgBox
VB:
Dim TillNo As String
TillNo = Target.Offset(, -1) & Target.Offset(, 0) & "Complete"
MsgBox "Till Number is" & TillNo
Target.Offset(, 10) = Evaluate("COUNTIF(C3:C123,""6017Complete"")")
Target.Offset(, 11) = Evaluate("COUNTIF(C3:C123,TillNo)")
see my attached sheet cotaining the following questions. in a day report sheet how should i count request matching the crateria of date and other conditions. in a monthly report a heavy conditional sum calculation which make slower sheets how can i make it faster.
View 2 Replies View Related# of Project Names that are under Status=Contract (Amount in $’s ) & # of Trks
# of Project Names that are under Status=V Verbal (Amount in $’s) & # of Trks
# of Project Names that are under Status=No ($ Amount lost) & # of Trks lost
# of Project Name that are Lost to (Grandview) that are under Status=No ($ Amount lost) & # of Trks lost
Must be able to add more lines & have instructions so I can do it myself for the future.
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 RelatedI have a cell that I need to make a calculation if certain other cells =Y, or to show "0" if the other cells =N. I can do the calculation for the Y part but how do I add the N part of it? Here is what I have:
=SUMIF(J19:J40,"Y",H19:H40)*0.08
I have a list of people to send drawings out to. I have already made the selections am just trying to work out how to simplify the output so as to use as a field in a data file to go onto a letter.
"| A01 | A02 | A03 | A04 | A05 | A06 | A07 |" are for Joe Bloggs
( | are cells)
In another cell, i would like to merge them together like so
"A01-A07" are for Joe Bloggs
And also as follows
"| A01 | | A03 | A04 | A05 | | A07 |" are for John Smith
would like this to be
"A01, A03-A05,A07" are for John Smith.
So differing conditions depending on what happens in the range. I have had a couple of attempts at this using a VBA concatenate command, I can get them to display like "A01,A02,A03..." with a selected separator, but cannot get it how i want it.
What formula do I use to populate certain cells (E5:E10 and J5:10) based on match with condition (E3) with cells from and in the ranges C2:C73 and D2:D73 without creating milelong IF formulas? I am almost at the goal... past 10pm here in Thailand and still at the office
View 2 Replies View RelatedI am having difficulty calculating the number of total overlapping days between several date ranges for each item in another sheet which has unique items
I am using Excel 2003 and my data looks like this:
Sheet-1 Sheet-2
Item - Sent Out (A) - Received (B) Unique Item Number of days excluding overlap days
1234 01/06/2010 - 30/06/2010 1234 -
4321 02/06/2010 - 16/06/2010 4321 -
1234 09/06/2010 - 10/06/2010
4321 21/06/2010 - 25/06/2010
1234 23/06/2010 - 25/06/2010
4321 23/06/2010 - 29/06/2010
I have used the below formula found from the earlier post but need to add a condition calculating the days for each item.
=SUM(IF(MMULT((NETWORKDAYS(ROW(INDIRECT(MIN(INT(A2:A7))&":"&MAX(INT(B2:B7)))),2)<6)
*(ROW(INDIRECT(MIN( INT(A2:A7))&":"&MAX(INT(B2:B7))))>=TRANSPOSE(INT(A2:A7)))
*(ROW(INDIRECT(MIN(INT(A2:A7))&":"&MAX(INT(B2:B7))))<=TRANSPOSE(B2:B7))+0,ROW(A2:A7)^0),1))
i dont understand why this code is not working. i get run time 1004 application or object defined error. basically i want to count column 11 if there is a value in column 2.
Public Sub offloadDoor()
Dim unassigned As Long
unassigned = 0
For rowvar = 18 To 504
If IsEmpty(Sheet2. Cells(rowvar, 11).Value) = False Then
If IsEmpty(Sheet2.Cells(rowvar, 2).Value) = False Then
unassigned = unassigned + 1
End If
End If
Next
Sheet8.Cells("b3").Value = unassigned
End Sub
I have a table in Excel:
The first row is time in years.
The second row is method name,say,"A","B","C".
I want to count the number when the time is less than 5 years AND "A"
method is adopted.
I tried this:
count(if(AND(C2:Z2<5,C3:Z3="A"),C2:Z2) but it didn't work.
how to revise the formula?
In the mean time, count(if(C2:Z2<5,C2:Z2))worked as well as
countif(C2:Z2,"<5")
I'd like to use a sumproduct function to count 2 conditions. I want to add the number of times the number 0 is entered in Column D when a 1 is entered in the same row within Column C next to it.
I'm using the formula below yet its wrong.... it gives the answer of 7 rather than 1 (see data in attached file).
=SUMPRODUCT((C3:C124=1)*(D3:D124=0))
I have a worksheet with 3 columns in it. these are entitled "area", "uploaded" and "status". uploaded will be a numerical value and status will either be "awaiting signoff" or "completed" what i need to do is list all of the different areas and add the "uploaded" values together IF the status is completed.
View 4 Replies View RelatedWhat formula would I use to count a cell only if it met one of three conditions?
ie only if the cell equalled 1, 2 or 3 but not 4?
40,000 rows,
Column A is a Port Code . . . always 4 digits
Column B is a 2 digit code representing a mode of of transportation.
I did it the "brute force" way of concatenating the two columns into column C, then sorting and subtotalling column C . . . .
There are a variety of tabs on this database. These tabs track the large customers and specific brands.
In the “Sales Rep Calendar” tab, I have attempted to calculate how many quotes are established 1) per Month and 2) per OS Sales Rep for 2007.
Using formulas such as: ...
I'm trying to create a calendar overview of transactions from a raw data list. the result should be an amount, based on the number of occurrences of a specific transaction type at a particular date. Thus, the counter shall be based on two conditions. I've tried COUNTIF, INDEX, MATCH and VLOOKUP combined but I don't seem to "go all the way".
View 1 Replies View RelatedI have a need for a formula that is able to count the data in column that coresponds to data in another column.
Example work sheet attahced for a quikie look.
Ive started using the sumproduct function to count multiple conditions which is useful
howveer if i want to count those records in one column that meet a condition and those records in another column that meet anyone of a number of conditions how can i do that?
the only way i can think is like the below
=sumproduct(--((columnA=apple)*((ColumnB<>Red)*(columnB<>Yellow))))
Rather than having to eliminate red and yellow i would like to say is green or blue.
I'm working out a schedule for work. Row 1 contains 31 days(columns), Row 2 28 days, Row 3 31 days...and so on for the 12 months of the year.
I've formatted each Friday, Saturday, Sunday and Holiday with color. Fridays are blue, Saturdays are green, Sundays are yellow, and Holidays are red. Monday-Thursday are no color.
Next, I fill in each day with an employee name.
Now the hard part...I want to count the number of times an employee name falls on a Monday-Thursday, Friday, Saturday, Sunday and Holiday. At the bottom of the worksheet I'd like to see something like this:
Jones:
Friday 4 (total number of days jones is in a blue box)
Saturday 5 (...on a green box...and so on...)
Sunday 3
Holiday 2
Monday-Thursday 50
For each employee name. Sounds easy, right? I can't get it to work!
I am trying to count the number of entries in range BH3:BH621 when the cells in range B3:B621 = "Acting" and the range D3:D621 = "Feb"
I can do it with either the B range or the D range, but not both together.
I have 3 sets of data - Process, Step, and Time Range. I am trying to generate schedules based on Process, with Step being the vertical axis, and Time Range being the horizontal axis. Hence, I'll have schedules showing that for each Process, the number of cases that each Step that has taken, for example, "0-7 Days", "8-14 Days", etc.
I have four Processes in total - A,B,C, and D; 15 Steps from 1 to 15; and 7 Time Ranges. I have attached a sample .xls showing the schedules that I would like to popuple the counting onto. A little more details, not all Processhas all 15 Steps, i.e. Process A has Step 1 thru Step 9 only, Process D has Step 1 thru Step 15 excluding Step 11 & 12I am actually creating a template where data will keep on expanding and updatingwould prefer excel formula rather than VBA code as I am not very familiar with what to do with VBA codes
I would like to write a macro that would count observations from a database extract based on specific conditions and generate a report.
My database extract looks like this :
Client name
Info1
Info2
Info3
Bob
aaa
aaa
aaa
[Code] ....
I would like to have an efficient and simple VBA code to count the observations. Something that would store the result of query like those:
# 'client name' = Bob : 2
# 'client name' = Bob AND info1 = aaa : 1
# Info3 = bbb : 2
# 'client name' = Bob and Info1 = aaa OR Info1 = bbb : 2
I am aware of DCOUNT function, but I am not too sure how to translate it in VBA. I also think that using autofilter and counting the visible rows may be subject to bugs.
I have an issue log containing (separate) columns with dates, numbers and text.
One column (A) has the status (open/in progress/pending/closed/re-opened)
One column (B) has raise dates
One column (C) has close dates
One column (D) has the type of track (this is text, 3 types)
I want to count the amount of cells that have a close date before date x, given they are open (in Column A), and for a specific track (column D).
After creating a new sheet with a date range in one column encompassing all dates that occur in my column B & C, I tried to come up with a formula to count how many cells, and consequently how many issues, were open (or closed) on a particular date per track and as a whole.
So for instance; count cells in column C if Column C is before date 101112 and Column A is open and column D is "Build".
After trying COUNTIF and SUMPRODUCT the conclusion is I can't get it right.
I'm trying to count the number of occurrences where two conditions in a table are true.
I have a table that has two columns for ratings; impact and probability. Each can be scored 1-5 This creates a matrix table of possible scores from 1 - 25 (image attached)
I want to COUNT the number of items in each of the boxes (not the total score). For example, how many are Impact 5 and Probability 5 (25 total); how many are Impact 4 and Probability 2 (8 total), and so on. Basically a count of the each of the intersections in the matrix.
Something like "Countif Impact is 5 AND Probability is 5"
Is it possible to count something once, checking for multiple conditions?
I want to count all instances if the following conditions are true. In quotations, are the names that I am using for column ranges. Here are my conditions, I want to count the rows that have the following conditions.
When "dates" or J2:J25 is less than or equal to today's date
AND
"HTeam" or W2:W25 is equal to Civil
AND
"Percent" or K2:K25 is equal to 100
Assume this Data in a spreadsheet
I want to count blank "PI" based on "Product name" & "Product date" occurence together
So I created intermediate field "IS Blank", and I dunno what formula can give me the below results
My obective to get this result
CountBlank for PI = 3
--------------------------------------------------------------------------------
Product Name..... Product Date.... PI...... IsBlank "PI" [Desired Formula output]
xxx .......................ddd ...............Blank.................. 1
xxx .......................ddd ...............Blank.................. 0 (counted above for same xxx&ddd)
xxx .......................ddd222 ..........Blank.................. 1 (PD changed to ddd222)
yyy ......................ttt............... Blank....................1 (another product,yyy)
yyy .....................ttt ...............Blank......................0 (same product and date, so not counting again)