Use Function As A Criteria In A Counfifs?
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
ADVERTISEMENT
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
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
Jan 21, 2014
Criteria:
U4= 12/01/2013
Data:
(A) (B) (C)
01/01/2013 02/01/2013 12/01/2013
1 3 4
2 2 1
4 1 1
Result:
Small - looking for smallest to fifth smallest (separate cells B5=1, B6=2 etc.)
View 1 Replies
View Related
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
Feb 4, 2010
MathScienceLanguageOverall
PassPassPass----
PassFailPass----
PassFailFail----
FailFailFail----
What formula should I use to show "Pass" if a student passed all three subjects, "FAIL" If student did not pass in any subject and "TRY AGAIN" if he passed at least one subject? I can't seem to accurately plot "IF" formula. Should add in "AND" / "OR" as well?
View 5 Replies
View Related
May 5, 2009
Bid Manager Probability of Success Total Fee
cp 25% 675,000
rz 40% 18,383,541
nw 50% 8,700,000
cp 10% 4,500,000
ap 50% 1,200,000
kd 50% 49,500
sl 25% 3,200,000
bc 20% 310,000
sl 20% 3,460,000
nm 30% 6,750,000
at 50% 23,000,000
ap 30% 367,500
rn 40% 13,750,000
I have a list of commodity and percentages of success with fees in the 3rd column
I need a function to count the records with multiple criteria e.g greater than 20% but less than 30%.
Second i need a function when it complies to previous formula it should give fee total pertaining to that count.
View 2 Replies
View Related
Aug 21, 2007
I have a condition that it only applies to criteria before a certain date but I cannot get it to give me the right answer.
Original Formula ...
View 3 Replies
View Related
May 22, 2008
I would like a consistent OFFSET function formula across a row but my columns move from monthly to semi-annual periods at a point in time. How do I handle the move from monthly to semi-annual periods in my OFFSET formula?
View 4 Replies
View Related
Jun 21, 2008
I would to use the subtotal command and have it exclude rows from another column in addition to obeying an autofilter:
A B C
1 Area Rep Name Amount
2 Quota John 100
3 NW John 200
4 NW John 300
I want to use the subtotal(9) on the amount column, but I don't want it to include rows where the value Quota is in the area column. The idea is that the amount for a given autofilter condition (rep = john) should only show his forecast amounts but not his quota number for his area. DSUM includes hidden row as does sum
I have tried
subtotal(9,sum(C2:C4*(A2:A4<>"Quota")))
but that does not work
=SUM(C2:C4*(A2:A4<>"Quota"))
works but does not let me filter the data to only show values for john versus all reps
View 5 Replies
View Related
Aug 21, 2013
Excel.xlsx
Attached is the example of the data, I am trying to sum the amount applying 2 criteria, within a particular month and other category. The formula tried is
=SUMIFS(D5:D10,C5:C10,"*June",E5:E10,H1)
However it is giving a value of zero only. I have tried other combinations as well.
View 4 Replies
View Related
Apr 20, 2014
I need to do a SUMIFS with 2 sets of Criteria, but I need the second Criteria to be an OR Statement. The numbers I am adding up is in Column E, with any where from about 20,000 to about 60,000 records (will fluctuate all the time). The first Criteria is easy - match up the name in AG12 with any of the names in Column C. The second criteria is to match up the numbers in Column A with any of the numbers in AD1 through AD40.
I tried this but it didn't work: =SUMIFS(E:E,C:C,AG12,A:A,AD1:AD40). I've alos tried a million other variations of that, imbedded SUM(IF( statements, SUMPRODUCT statements, and DSUM statements and no luck. I can't find anything that allows me to make that second second criteria look at the numbers in Column AD as being AD1 or AD2 or AD3 so on and so forth.
View 3 Replies
View Related
Aug 10, 2014
I'm working on a spreadsheet that I need to return a value to "Unit Price" field in worksheet "Master Inventory" based on matching the "Product" field and the "Construction" field from the "Unit Pricing" worksheet.
In essence, I would like the "Unit Price" field to match the "Product" field from the "Master Inventory" sheet to the "New Product Description" field on the "Unit Pricing" sheet, then match the "Construction" field on the "Master Inventory" sheet to the column headers on the "Unit Pricing" sheet and return the value that corresponds to both criteria.
Ex: On the "Master Inventory" sheet, I would like the "Unit Price" field to match the "Product" (Book Browser) to the "New Product Description" (Book Browser) on the "Unit Pricing" sheet and then return the value where the "Construction" (Laminate) matches the column header (Laminate) on the "Unit Pricing" sheet which would return the value of "$240.00".
I've tried using a vlookup function, vlookup/match function, index/match function and an index/match/match function. I've attached a sample workbook.
View 3 Replies
View Related
Feb 5, 2014
=IF(LEN(A480),SUMPRODUCT((RIGHT(Sold!$D$15:$D$8998,LEN(A480))=A480)*(MONTH(Sold!$M$15:$M$8998)=12)),"")
I would like to edit the function above and add a third criteria. If Sold!S:S,"Returns" to my sumproduct.
I tried to edit it myself with the function below but it didn't work.
=IF(LEN(A480),SUMPRODUCT((RIGHT(Sold!$D$15:$D$8998,LEN(A480))=A480,Sold!S:S,"Returns")*(MONTH(Sold!$M$15:$M$8998)=12)),"")
View 3 Replies
View Related
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
View Related
Dec 2, 2008
I have the following formula in a sheet to record basketball stats that I am putting together.
=COUNTIFS('Running Game Log'!A:A,"=Jarod",'Running Game Log'!C:C,"=2 pt miss")
This formula counts from a list that is generated during in-game recording of stats & obviously counts the number of times "Jarod" misses a "2 pt shot".
My question is can the criteria "Jarod" refer to a value in a cell, rather than the specific name "Jarod" as I want to be able to change player names easily, rather than have to edit this formula every time.
View 7 Replies
View Related
Apr 30, 2009
I have table product that include (wide 1,wide 2,thick 1,thick 2,long 1,long 2,Price) And I have table transaction than have wide, thick, long and price)
I want that price in table transaction fill automaticly from table product where ( wide between wide 1 and wide 2 ) and
( thick between thick 1 and thick 2 ) and ( long between long 1 and long 2 )
excelformula.gif
pricing.xls
View 2 Replies
View Related
Mar 21, 2012
I have two documents, and I need to search data in document 1 based on information given in document 2. On document 1 I have ID number and date range and on document 2 I have ID number and date. Date needs to be in the date range, and ID numbers must be the same.
Please see tables below for the reference.
Document 1
Document 2 ID Start date End Date Data
ID Date Data 11 01/01/2009 31/12/2010 Data 1
11 05/05/2011 22 01/01/2010 31/12/2010 Data 2
22 08/08/2010 22 01/01/2011 31/12/2011 Data 3
33 01/01/2012 33 01/01/2012 31/12/2012 Data 4
11 02/02/2009 33 01/01/2010 31/12/2011 Data 3
11 01/02/2011 31/12/2011 Data 2
View 2 Replies
View Related
Jul 20, 2012
Find Function with Multiple criterias. Ex-
ADC XE AD ADKO R Ad Know Op
ADC XE AD ADKO X Ad Know
ADC XE AD ADKOAd Know
KNE XE KN ACDA
If this is my data in Column A, I need to find the names which contains " R " and " X " in 16th digit. I can do it using find formula twice but is there a way to do it in on shot..?
The formula I'm using is =FIND(" R ",A1,16) it gives me numbers like 16, 17, 19, 20 etc but I'm interested only in 16 digit so I just filter for 16 and copy the data to seperate tab.
View 9 Replies
View Related
Dec 12, 2013
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 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
Jul 30, 2007
In an excel sheet A1 to ...... colum i have some cirteria. If I filter a criteria i should get the count for it. Kinldy let me know with out using VB how is it possible.
View 9 Replies
View Related
Jun 7, 2006
I need to count rows that meet 2 criteria.
I have seen this help page
http://www.ozgrid.com/Excel/count-if.htm
but that counts rows with "criteria 1" OR "criteria 2"...
I need to count rows that fulfill "criteria 1" AND "criteria 2"
ie - count the rows that have todays date AND a cell that says "COMPLETE"
ideally it would be as easy as "=countif(A:F,"today()","COMPLETE") but that doesn't work... any way around this???
View 9 Replies
View Related
Jul 29, 2006
I'm trying to find the best Macro or Formula to get this done.
Sheet 1 has a list of salesperson in column "A" and the total numbers they made on Monday (in the same row, column "E"), Tuesday (In the same row, column "F"), Wednesday (In the same row, column "G"), Thurday (In the same row, column "H"), and Friday (In the same row, column "I"). I also have a sheet for each individual salesperson. I need to find a Macro or a Formula that I can use to give me the average of the numbers if "A5" = the salesperson in Sheet 3 then avarage the numbers from "E5" to "I5" and I want the results to show up in cell "D35".
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 26, 2014
Need to use CountIFs formula to evaluate multiple conditions in the same column. Here is the formula I tried. It works for Resolved but returns 0 when i try to add Duplicate.
=COUNTIFS('P12 Source'!H:H,A18,'P12 Source'!F:F,"Resolved,duplicate")
Also used this formula with success by referencing a cell that had "Resolved" in it but when I tried to add another cell with "Duplicate" it again returned 0.
=COUNTIFS('P12 Source'!$H:$H,A19,'P12 Source'!$F:$F,'P12 Source'!$F$75)
View 8 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
Feb 9, 2010
We're selling language courses that have a different price depending on when a course is taken. We're trying to build a price quote application that needs to look up a certain school and course and price driven by a start date.
How can I build a lookup function that says: pick the price of school X and course Y when the start date falls between dd/mm/yyyy and dd/mm/yyyy?
I manage to build a look up function with MATCH and INDEX when the condition of start date is exactly matched but dont know how to instruct it to match a value between a start and end date.
View 14 Replies
View Related
Oct 4, 2008
So here is my little dilema,
I have a worksheet that I need help with using SUMIF function.
Basically how do i use this function to search for 2 sets of criteria as opposed to just 1.
View 8 Replies
View Related
