Database Function Criteria Range
Jan 22, 2007
I am trying to use the function DGET() to re-organize the information on the "DbCalc" sheet in the attached workbook. This formula can be found in C2:C5 in "purple. I am referencing the worksheet "Exp Rpt" as a database and match info based on two criteria. I can do this using an array formula like this ={SUM(IF((A1='Exp Rpt'!B$9:B$10000)*(A2='Exp Rpt'!E$9:E$10000),'Exp Rpt'!H$9:H$10000,0))} , however with the amount of data that I need to reference this array is soaking up all my memory. I would usually turn to access to do this, but the person that will be utilizing this tool only knows excel.
View 11 Replies
ADVERTISEMENT
Apr 15, 2009
I have the following formula below to return "C" if the value in the cell is "C", and nothing if not.
I want to expand it to return "C" if the value in the cell is "C" or "D", for a range between C2:H2.
=IF(C2="C","C","")
I'm have trouble with this easy adjustment.
View 3 Replies
View Related
Mar 1, 2014
I 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 Related
Dec 5, 2013
I am trying to use the AVERAGEIF function to give calculate averages based on dynamic data. The formula I want to work is:
[Code].....
but this returns the "#DIV/0!" error.
However,
[Code] ....
returns the correct value.
I want to be able to calculate averages of only the cells that are greater than the value of cell D6. Is there a way to do this?
View 3 Replies
View Related
Feb 27, 2008
I have a worksheet with 6000 rows (W1), and another with 2500 rows (W2).
I need to check whether the values of W2 are found in the second column of W1. As in if(iserror(search(valuex,worksheet2!B2)),"",A2) ---> resulting in something like:
If the value is found in the cell B2 of column B on W1, then return its reference which you find in A2, otherwise leave a blank.
I need to check all 2500 values in all 6000 rows.
I know for sure that I will have limited hits (max of 200) so I would like to create a list on W2 (the values) where I check if they are found in W1 and return only the 'hits'. I would like to filter out the blanks.
YOu can filter the blanks, I know, but you need to have a 'full' version (I thought) with all the blanks and the hits and then and only then you can filter.
But is there a way how I can use the advanced filter, with a criteria range using a function. Something like: criteria range --> if(iserror(search(valuex,worksheet2!B2)),"",A2) is not equal to "".
View 14 Replies
View Related
Aug 19, 2014
I am trying to SUM the total task done per date. The problem is, (for the sake of this problem) the DATE is in Column B and the Task Total is in Column A. I know that SUMIF is not possible because the SUM RANGE should be always on the right side of the CRITERIA.
My question is, is there other formula other than SUM IF to figure this out? or I have no choice but to Interchange the data in a matter that SUMIF function will run.
View 6 Replies
View Related
Jan 4, 2013
I am building a database in excel where I have the number of days across the top (horizontal) and in Cell A2:A300 I list our product lines (that is repeated based on different divisions). Beside each product line in row b3:iv3 I have sales data for each working day.
In a new tab I want to formulate the total of ProductA on day 240.
A B C D E F G H
1 Region Product 235 236 237 238 239 240
2 RegionA ProductA 25 31 15 18 10 5
3 RegionB ProductA 21 13 10 5 28 10
4 RegionB ProductB 21 13 10 5 28 10
5 RegionC ProductB 21 13 10 5 28 10
6 RegionA ProductA 25 31 15 18 10 5
7 RegionA ProductC 25 31 15 18 10 5
In another Tab summary of Product Totals:
Day235 Day236 Day237
Product A 76 75 40
Product B 42 26 20
Product C 25 31 15
I tried many formulas for example:
=SUMIF('2012 Sales'!$C$3:$IS$113,$C1&$F$4)
View 9 Replies
View Related
Jul 18, 2014
i have database where are some data for several shops i need macro which will copy data for exactly that shop which i will choose in summary sheet, for better understanding i also attached excel file.
View 1 Replies
View Related
Aug 20, 2009
when it comes to using an excel sheet (which is already built and rather large) for functions which are more suited to database functions.
The excel contains audiotaped medical info in columns:
question
physician name
diagnosis/subject
timecode
label1 (checked box "on")
label2 (checked box "off")
comments
each of these are sectioned by audiotape with headers --
looking something like this --
TAPE 1
diagnosis/subject1
then under that, columns
question | physician name | timecode | label1 | label2
then
diagnosis/subject2
then under that columns
subject/question | physician name | timecode | label1 | label2
then
TAPE 2
with similar info
so each TAPE has numerous DIAGNOSES covered under which there are various questions asked and answered by individual doctors.
(see link to dummy file for visual)
[url]
I need to be able to search on a group of criteria simultaneously:
# physician’s name
# subject
# on-label
# off-label
and return the soundbite in a user-friendly way so that the entry-level desk people (with little or no excel expertise) can retrieve it.
YES, it was not set up to do this, but this is what i've got. so i'm hoping that before i go down too many dead-ends, someone can tell me which is the best way to do this.
Looks like VLOOKUP can only really search on one column and filters would require too much expertise for entry-level employees, but should i look into VBA or Macros. or do i need to import this into Access -- or learn SQL to interface with it on a user-friendly website?
View 9 Replies
View Related
Jan 19, 2007
I would like to return the lowest non-zero number in the second column (B), based on the value in column A
in this example, 1 should come back
Example numbers
A B
22 0
33 0
22 1
22 2
33 2
22 3
I started out using:
=IF(AND($A$1:$A$6="22",$B$1:$B$6<>0),MIN($B$1:$B$6),"")
But it is not working for me.
View 9 Replies
View Related
May 29, 2009
I do have an excel workbook containing two sheets:
Sheet1 contains a list of 30 criteria, every one with its own check box (TRUE/FALSE).
Sheet2 contains a database of items, which columns contain the 30 criteria mentioned above. (So sheet1.criteria1 can be the only value of sheet2.column1)
I am trying to program a macro, which can be assigned to kind of a "submit"-button in the first sheet. Its purpose should be to open a new third sheet and listing all the items from sheet2, which contain values checked=TRUE in sheet1.
View 10 Replies
View Related
Oct 2, 2007
Cell A1 is HEAT_CODE
Cell A2 is W5H
In cell
=DMIN(Database,"Cost",A1:A2) return the proper value
I need something like the following so I can use fill down.
=DMIN(Database,"Cost","HEAT_CODE"=A2)
the above returns #VALUE! error.
View 6 Replies
View Related
Apr 16, 2012
What I am trying to do is extract all the data from a excel database based on criteria. When i specify the site and month I need to pull all the corresponding data and write it to a worksheet. Below is an example. I am not allowed to show all the data it is confidential.
Site Date Operator Month
Chicago 12/3/11 Daniel December
San Jose 4/8/12 Mike April
New York 4/8/12 John April
View 1 Replies
View Related
Feb 1, 2007
I have a database that has outgrown excel (over 77,000 rows now) so i now have it in Access. I want to replicate the following situation I used to have using lookups in excel with a query to the Access database: Paste a list of identifiers into an excel sheet. Values corresponding to the list are returned from the database in adjoining columns. The list can be from 10 to 300 cells long an returns data in 14 columns. I have had a go with the Import external data wizard which generates a query but have not been able to work out how to base the query on a list of cells in excel and have the query return values for each of the identifiers in the list.
View 2 Replies
View Related
Jan 9, 2012
I have made a database to record drawing numbers.
In column A is the drawing number and in Column B is the revision number.
I am trying to write a macro that will alert the user when they are trying to enter a drawing that contains BOTH the same drawing number AND the same revision number as one already in the database.
Since drawing numbers will be reused and only revision numbers changed when a revision is entered, I need the function to check both column A and Column B then display a msgbox if there is a match.
View 1 Replies
View Related
Oct 25, 2007
I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.
View 9 Replies
View Related
Nov 28, 2012
I'm trying to make a userform to show the results of a search from column A. This code will search the coulmn but when the results show it show's all ranges not just the ones that contain the string
VB:
Private Sub BtnFind_Click() Dim strFind As String
Dim c
Dim rSearch As Range
[Code]......
View 2 Replies
View Related
Mar 31, 2004
I am currently trying to create a database of products for my company. For each product I would like to include an image associated with it. I then want to have on another sheet a place where the user will click an error and be able to cycle through the products. As tehy cycle the associated image will pop up.
What I need to understand is after importing the image into excel, how do I associate that image to a cell so I can reference it in another sheet of the database. I am not concerned with how large the database will get, my pictures are quite small.
View 4 Replies
View Related
Nov 7, 2008
I'm trying to lookup a database named database and return a time in column A, based on criteria in cells a1 and b1 on another sheet, A1 would contain a number and B1 would contain a day from mon-fri
eg of Database
A B C D E F G
10:00 5000 Mon Tues
11:00 5000 Wed Thur Fri
and so on
if a1= 5000 & b1=Wed
how can I return 11:00
I have tried index and match =index(a:a,match(a1&b1,b:b&e:e,0))
View 9 Replies
View Related
Jun 24, 2013
I need a formula that compares 2 databases and returns a count if both values or within the specific range.
Column A is titles Column B is values The first database is simple the range is
View 2 Replies
View Related
Oct 17, 2013
I've used a countifs, but I'm having trouble doing a sum in a similar way. I have 2 databases and I will try to explain below. I need the to sum the values of database 1 if the second database is >= 20. So the value I would expect on this example would be 900. I would also like to be able to highlight which ones are elliminated. Such as coloring the text red.
Database 1
Eric 100
Jenny 200
Gina 300
Doug 400
Database 2
Eric 18
Jenny 20
Gina 34
Doug 55
View 5 Replies
View Related
May 18, 2014
Macro for AverageIFS, with multiple criteria in the same criteria range....
View 9 Replies
View Related
Sep 8, 2007
I have a database function in which I cannot use an "OR" operator. I can add "+" on to another (Ex. Sum(a1:b1)+sum(b2:c3)). The function itself is 80-90 characters long. So after I add a few together the cell runs out of capacity. I have a analysis that simply does not lend itself to listing out individual functions and then summing them.
View 3 Replies
View Related
Apr 13, 2007
In Mr Excel's Pod Cast on April 12th, he showed how to use the OFFSET function to define a range inside a SUM function. Then he had Conditional Formatting that would highlight the range that was being summed. Can anyone tell me what the formula would be inside the Conditional Formatting dialog box to get the OFFSET range to have a certain format?
View 9 Replies
View Related
Mar 4, 2008
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!
View 5 Replies
View Related
Jul 23, 2013
I have a function
VB:
Function f1(Matrix As Range)
'Does something and returns f1 = a double
End Function
And a second function which defines and constructs a matrix of doubles to use as an argument in f1 to return a double:
VB:
Function f2(dD As Double)
Dim MatrixRed() As Double
Redim MatrixRed(1 To dD, 1 To 10)
For i = 1 To dD
For j = 1 To 10
MatrixRed(i, j) = i * j
Next
Next
f2 = f1(MatrixRed)
End Function
I get an output error (#VALUE). I think it has something to do with MatrixRed not being a range anymore?
View 2 Replies
View Related
Dec 6, 2007
I have a large transactions table with 10 columns and more than 7,000 rows. One of the columns names the country in which the transaction has taken place (array named "country")
Another names the year of the transaction (array named "year")
And a third one holds the transaction value. (array named "value")
What I would like to do is to sum the transaction value of one country for one specific year? I.e. what was the aggregate transaction value for transactions in Sweden in 2005?
View 3 Replies
View Related
Sep 15, 2014
I run a countifs against a column of data, and can easily test for specific concrete criteria in other columns, but I want to run a function on the value in a column as a criteria.
For example,
countifs(data Name, "=Technical", data count, ">4")
is great.
But I want to check for some function on another column, e.g. "data number, square(x)<100", I cannot reference the indexed data value in the (data number) column as the "x" in the formula.
What I really want to do (!) is to test if a field is (not) in a list of matches, e.g.
..., data tag, NOT(ISERROR(MATCH(???,GoodValues))),
Where I can see if the tag for an entry is in a list ("GoodValues") defined elsewhere.
View 1 Replies
View Related
Aug 24, 2014
I was doing this task using sorting then adding column today morning one of my friend told i can do it with formula only, no need to using sort and adding new column
I just attached the sheet : without sorting.xlsx‎
View 5 Replies
View Related
Dec 15, 2013
I am stuck trying to add another criteria to an existing formula.
On my sheet "Data" i have the following formula in column AF2
=IF(Z2="","A",Z2-AE2)
If Z2 (Payment Date) is Blank then return "A" in AF2, however if Payment Date has an actual date in the cell then minus AE2 (Received Date) from the Payment date.
This gives me the "Receipt to Pay" number E.g. 6 days from receiving to paying an invoice.
I now need to add another criteria to this and i cant fathom it.
I need to do the following:
If Payment Date (Z1) is Blank and Invoice Status (T2) is "Loaded" then Minus Received Date (AE2) from Todays Date, while also retaining the last part of original Formula Z2-AE2 if the first criteria is not met.
(I have put =TODAY() in Cell AG1 for ease).
What do i need to do to add this into the formula. Do i need to use IFS function?
View 3 Replies
View Related
