I need a formula that acts very much like a DCOUNT formula. However, I need to count the numbers of instances where "09007" is in Column A IF the value in Column B is greater than zero OR if the value in Column C is geater than zero.
I would prefer a formula that can calculate this with only the existing data. In other words, I don't want to have to add additional columns with formulas to help get me to the results.
i'm trying to write a summary page for the data collect on the first sheet and would like to find a way of counting the number of records within certain date criteria (those that are more than 30 days old). now the crucial part is that the records have a second set of critical data, their Status, and i need to find only the records that have a particular status (in this example that is Pending and Open).
if you look at the second sheet you'll see that i can find records that are associated with a specific date, but not with a range of dates; which is particularly annoying because DCOUNT can find ranges of information when using other types of number.
OK, I have a list of sales that my semester long company has sold. In the midst of these sales we are sellling a versoin of the bumble watches. people can either buy a watch, (which includes a face and a band) a face, or a band.
So I have this giant list and only like 10 watches on there. I need a formula that will count how many watches and faces we sell.
I was initially thinking something along the lines of:
=COUNT(OR('Sales Journal'!D:D="Watch",'Sales Journal'!D:D="Face"))
=COUNT(OR('Sales Journal'!D:D="Watch",'Sales Journal'!D:D="Band"))
Which worked great until I had more than one watch in there.
I can't figure out dcount, and don't know if it's even the right thing to use in this instance. If you want I can post the sales journal..
Using the dcount function it appears that the criteria is not exact. I have typed in ="IP" but instead of getting only IP. I'm getting cells beginning with IP, eg IPp and IPe. When I use countif I don't get this problem.
View 3 Replies View RelatedI have created a register that gives the ages of the participants being registered. I need to figure out the number of boys and girls of different age groups. I used the formula =DCOUNT(database,column no,criteria) with criteria having two columns, Gender & Date of Birth. Under Gender was M, Under Date of Birth was >=01/03/2002 [dd/mm/yyyy]. This gave perfect results shwing me the number of boys less than 12 years old. However, wishing to make the formula dynamic I changed it to >=EDATE(TODAY(),-144) which is exactly twelve years ago. This formula gives wrong results and I can't understand why.
View 9 Replies View RelatedI am using Excel 2010.I have a Advanced Filter that has removed 2 list, with 3 criterias, List ( or DB1 ) is based off 1 item " Pay type ( hourly / Salary )" , then the 2nd List is based off 2 Criteria's Job type , and Less than a number, I am able to do the filter just fine, but i am requried to DCOUNT theses.
here are the ranges for the Default Table: A6:H45 (without headers)
List1 is in J6:Q27
List2 is in J33:Q37
*Both are without headers also*
I am asked to "DCOUNT"
List 1 based (A6:H45,"Type",L30)
List 2 based (A6:H45,"Job Type"+"Salary(has to be
I am trying to use the DCOUNT function to count based on two different criteria in adjacent columns is this applicable.
Situation:
I have column B that contains initials and I have column C that contains a type (of store) so it'd look like this
jp RET
jp RTO
jb RET
ma RTO
Rn CI
ma Fur
tc CI
ma RET
jp RET
and I want to have a count of how many RET's there are for Initials JP ect
I'm trying to do a Dcount (=DCOUNT($A$1:$M$229,Q$2,P14:V15))is there a way to do it without have to repeat the Criteria over and over?
Data Sample:StatePkg MeatCustomer #CT1MAMENHNJNYRIVT
Criteria Sample:StatePkg MeatCTHaveStatePkg MeatMAHave
I am trying to count unique records only in a column, if another criteria in another column is met. I got the Dcount to work by iteself, but I need to drive down the results more.
This is what I have:
=IF($G$2:$G$300="Q 000",DCOUNTA($D$2:$D$300,1,$K$2:$K$3),0)
The result I get is zero. In $K$3 I have =COUNTIF($D$2:D2,D2)=1).
I'm trying to return a count of specific text values in a named array using DCOUNT, and for some reason it's not working when it seems like it should. It's probably something simple that I'm overlooking.
DCOUNT works fine for numbers, but when I search for a text string as a criteria, even though it is entered as ="=textstringhere" DCOUNT does not return the correct count. COUNTIF returns the total count of all matching text strings in the array, but that's not what I'm after.
I've attached a sample workbook : DCOUNT.xls
I have a sheet called 'frontsheet' where users complete a number of fields and a second sheet called 'table'
When the frontsheet is complete, users press a button to submit the data to the table, it checks the fields are complete and submits the data - all this works fine.
However I would like to add a mechanism to check the first entered piece of data (asset number) against all the asset numbers already entered on the 'table' sheet.
I thought I could use a dcount to do this but after trying a multitude of methods I get the same error "sub or function not defined"
Code:
Private Sub CommandButton1_Click()
'Dim ta As Worksheet
'Set ta = Worksheets("Table")
'Dim fs As Worksheet
'Set fs = Worksheets("Frontsheet")
[Code] ........
As you can see I've tried a few ways and have a few 'remmed'* out lines. I'm starting to suspect the problem lies elsewhere, should I have the dcount happen without the need to click a button and how would I do that?
I'm not sure if using the Dcount formula is the right one in my example below, but I can't get it to work. Here is my data:
A
B
C
D
E
F
G
H
1
ThrFriSatSunMon TueWedThr2
2030101512
I'm looking for a formula that would count the number of Weekdays with data in it. So the answer would be 3. As I enter data into column F, G, H, etc. the count would go up and would always skip "Sat" and "Sun".
formula to count two criteria in the one column if there is a third criteria in another column. i.e. if column B = either "A" or "B" and column C="D". I have tried the countifs function without success.
View 4 Replies View RelatedI've attached a sample workbook to show what I am trying to do. I would like the formula to say "if Sheet2!A:A is "MON" and if Sheet2!R:R matches Sheet3!A:A, and if Sheet2!I:I doesn't match any of the values from Sheet1L:L, then I'd like the sum of Sheet2!F:F. It seems pretty simple but I've tried a million different variations of SUMIFS, SUMIF, IF, AND, etc. and I can't figure it out.
View 1 Replies View RelatedI can't seem to get this to work the way I need it to do. Let's say I have a 2 sheet workbook. Sheet1 is called "Order" and Sheet2 is called "005". On sheet2 (005) I have 9 columns that are populated. Row 1 is my header row and then row 2+ is all my figures. On sheet 1 (order) I want A1 to look at sheet2 (005) in F1:F10000 to find all cells that have "N" in the cell. I then want it to return value in column A that corresponds with the "N". I want it to list all the ones from Sheet2 (005).
Sheet 1 (order)
A
052611
052806
052843
Sheet 2 (005)
__A_____B____C_____D_____E____ F
052611_________________________N
154272_________________________Y
125485_________________________Y
052806_________________________N
125478_________________________N
052843_________________________N
how I can write is in a formula?
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)
I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}
I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message
Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)
Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)
-Didnt get thru this bit to start building on the Function! keep giving me #Value!
I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.
The range containing all the data
A
B
C
1
Cat 1
January 1, 2014
John
[Code] ..........
I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.
For example;
C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1
C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1
C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4
The best try I had for the formula in C7 was
{(INDEX($A$1:$C$4,MATCH(1,($B$1:$B$4>=B7)*(A$1:$A$4=A7),0),3))}
This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.
I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.
I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).
Here's data table:
CriteriaHeader 1Header 2Header 3Header 4
ZZZ5.0015.0050.00130.00
ZZ5.0015.0050.00130.00
Z5.0015.0050.00130.00
YYY5.0015.0050.00130.00
YY5.0015.0050.00130.00
Y5.0015.0050.00130.00
DDD5.0015.0050.00130.00
DD5.0015.0025.0075.00
D5.0015.0020.0065.00
RRR5.0015.0015.0045.00
RR2.5010.0010.0030.00
R1.503.0010.0025.00
UUU0.751.505.0020.00
UU0.751.505.0020.00
U0.751.505.0020.00
P0.751.505.0020.00
T0.100.105.0018.00
CriteriaNumberValue
DDD10.00Header 1>>>=INDEX($I$9:$L$9,MATCH(I29,INDEX($I$10:$L$26,MATCH(H29,$H$10:$H$26),)))
I have two columns. Column A had numbers and column B has names. I need a count from column A for each name in column B.
View 1 Replies View RelatedCol C = Text and Col F = dates
I would like to count the # of times a value occurs in Col C based on a date
range in Col F.
If i have the following layout of data
Column 1 Column 2
January 1000
January 1234
February 1300
March 1600
January 15
March 123
April 234
January 3000
I would like a formula that adds all the January numbers together returning a result of 5249
To move this one step further i would eventually need to add these numbers based on quarters, for example if the value is January February or March in column 1 then add the numbers in column 2.
I'm sure i have done this before using a countifs maybe but my mind has drawn a blank
I am trying to get an average from one column based upon criteria from an adjacent column. The number of days to close a case for race columns Black and White are listed in B5:C16 and E5:F16 and H5:I16. I need a formula to calculate the average days taken to close cases for Males and then the same for Females. Sample below: ...
View 12 Replies View RelatedI have an personnel file with employee info, and I want to create a macro that will look in the "Master" worksheet at the Department column ("I") for anyone in Benefits, and then copy their name from the Name column ("D") into the "Benefits" worksheet. The names should begin pasting in cell "D3" but will recognize if a cell already has a name in it and then paste in the cell below that.
Here is what I have:
Option Explicit
Public Sub RatingbyDept()
Dim Dept As Range
With Sheets("Master")
For Each Dept In .Range("I2:I1000")
With Dept
I keep getting error 92 - "For loop not initialized".
I have 2 columns; 1 with "dates" of incidents, the other with "number of incidents". The dates are not in any order but I want to be able to count "number of incidents" for certain time periods. e.g. If dates are between 01/02/14 and 28/02/14, what are the number in incidents. This number would be represented on a new worksheet.
I've looked at COUNTIF/S, SUMIF but just cant get there.
I have attached a work book, with worksheet Attendance which I am trying to calculate the sum of amounts in column N with the criteria from column C and E.
e.g Criteria Column C Jul-01 to Jul-31 or Sep-01 to Sep-30, Column E Alicia or Amelia.
I have a column containing text values eg M1, T2, M3, A4 etc. and I am trying to return the value (numeric) in a second column to a cell when the text value in the first column is matched.
View 4 Replies View RelatedThe below sample is an accurate example of a much larger set of data. Column H will be my desired result.
I need a macro that will look at the matching values (numerical value of month) of Column F, then enter the sum of the corresponding values (rows) contained in Column D for that month. The sum of each month should be placed in Column H at the last value of the row for each particular month.
A
B
C
D
E
F
G
H
1
Item#
Description
Quantity
Month
Year
2
1001
[Code] ........
I have a workbook where I am trying to COUNT the values greater than zero in column "J" based on a selective criteria of column "B".
In other words, if the date in a column "B" cell (B3:B78) is greater than the date in A86, I want to count the number of cells whose corresponding value in the "J" column is greater than "0". And then I want to take this selective COUNT and divide it by a COUNT of all the cells in the range "J3:J78".
So, what I am trying to do is:
FORMULA /COUNT(J3:J78)
I need to write a couple of functions. Firstly I want to do a count on column B (project $) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].
View 4 Replies View RelatedI have a sheet of 2 coulmns , the first containing numbers) , the second containing (objects) , these numbers are as references for the objects ,
however , every object may have different referencies ,
i.e( car = 1 in a row , in another row car = 2)
I tried to use VLOOKUP combined with OR , in order to return the car , if I entered any of references relating to it.