Criteria Values In COUNTIFS Function
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 ingame 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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Month Criteria In Countifs ????
check out the COUNTIFS function on Data Tables Sheet > Cell D29 and pls. let me know what's going on. I need is : To calculate the number of items on system data tab which has the "Pending" status and items which belongs to month "Jan10" under a particular person selected from B 27 cell (Data tables tab). I solved it by using >=010110 and <=310110 criteria but i don't think its nice idea .... I tried to use AND(function by got failed ...........
View Replies!
View Related
Countifs Function
I am using the COUNTIFS function. I can us it with simple criterias but unsure to go about this criteria. I would only like to count the cells if the range in question is equal or greater than S3 but is equal or less than T3. I don't know how to use the => signs.
View Replies!
View Related
Countifs With Month And Year Function
I have a column titled "Start Date", "Month/Year", &"Total". I want to count the number of dates that appeared in the "start date" column that have the same month and year of the "month/year" column. That count should be in the total count. For ex, I want the first total (april 2008) to be 1, the second (may 2008) to be 1, and so on. I tried using countifs month and year of the start date is the month and year of the month/year column. Not working. Here's my data: ....
View Replies!
View Related
COUNTIFS And Paste Special Function
I have a question regarding COUNTIFS. I have data arrayed vertically and horizontally on a worksheet. Date  Column 1 Column 2 etc. Criteria data (flight information) is listed under these columns. I'm trying to get the COUNTIFS formula to count the number of times a particular flight occurs within a date column. My formula is =COUNTIFS(C27:G24 (this is the field that contains the flight info), C6 (this contains the particular flight I'm looking for), C16:G16 (this contains the date range, C5 (this contains the date I'm looking for) When I use this formula I get the result #VALUE! back. I also wanted to ask if there is any way to use the paste special function but not lose the original formatting/formulas from the cells one is pasting from.
View Replies!
View Related
Formula/Function To Return Multiple Values Based On Criteria
I would like to be able to use the Vlookup function to return more then one value as a result of the criteria. I have a cloumn of dates that populates the X axis of a gantt chart with data whilst the Y axis will be populated by a site reference resulting in a program of work, the Y axis data is the result of a vlookup function. My difficulty arises however with multiple sites, for instance where two or three sites will be visited on the same day. The vlookup function will only return the first value it finds in a range to the formulated cell. The result being a missing site(s) from the gantt chart / work program. is it possible to return all values to a cell i.e. site1, site2, site3. using a vlookup or do i need to use another method of doing this?
View Replies!
View Related
Formula To Pull Cell Values Similar To A SUMIF Function (SUMIF(range,criteria,sum_range))
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 Replies!
View Related
Convert Absolute Values To Negative Values Based On Criteria
is it possible to write a macro that will convert absolute values into negative based on criteria? Data gets downloaded from in house system which comes out as all absolute values, now i need the macro to covert absolute values to negative if the amount is either "Our Reciepts" or "Our Delivery" Below is a small sample of data, real data is around 100 rows Col DCol EOur Receipts1231Our Delivery1231Delivery Settlement1323
View Replies!
View Related
Replacement For COUNTIFS
I am looking for a replacement to the function COUNTIFS that will work with Excel 9703. I have attached a sample of the document which will hopefully demonstrate what I am trying to achieve. On the sheet labelled 'Data' there is four headings (MerRefs, JCPRefs, Self Refs and Other) for the results beneath these headings I have used the Countifs function. I have attempted to use the SUMIF as an array formula but couldn't get this to work. Also in the hope of killing two birds with one stone, I created this in Excel 2007 and in the past when I have saved documents as 9703 documents it used to warn me of any incompatible functions included within the doc and now it doesent. Where can I switch that setting on?
View Replies!
View Related
Countifs On Two Different Variables
Say I have a list of part numbers, and each part number has an X or a 0 next to it, depending on my own set parameter. How do I then report that data on another tab so that it counts how many there are in a set area AND if its an X. At the moment I have this:
View Replies!
View Related
Countifs On X Axis
I'm looking to count the following: A B a 1 a 1 b 0 b 1 c 0 a 1 If column A=a AND column B=1, add column B on the same axis. I can only get a function that adds all B column if A=a. The function should equal 3. Cells B1, B2 and B6 should be the only ones counted.
View Replies!
View Related
Multiple Countifs
I have a list of information regarding jobs that need to be carried out by a number of companies by a number of dates. What I want to count is the number of jobs that company A need to caary out by a specific date and then the number that company B need to carry out by the same date. The formula basically needs to say countif column a equals company A and column C equals 11/11/09. I have tried numerous combinations of IF, Countif, SUMif, And etc but can't get the formula to work,
View Replies!
View Related
Nested IF And COUNTIFS Functions
I would like Excel to look at column A, and if there is a number0, count it and go to the next row. If it's =0, then go to column B and see if THAT number is 0. If it is, count it; if not, go to C. So my data looks like: A B C 0 2 3 4 0 0 0 0 0 I only want it to count one time per row, so right now I am using =IF(COUNTIFS(A2:A30,"0",B2:B30,"0")0,COUNTIFS(A2:A30,"0",B2:B30,"0"),IF(COUNTIFS(B2:B30,"0",C2:C30,"0")0,COUNTIFS(B2:B30,"0",C2:C30,"0"),0))) I also tried it with a SUMPRODUCT to simplify but both formulas are getting me the wrong answer. I'm using Excel 2007.
View Replies!
View Related
Date Range And COUNTIFS
I need to look at a series of data once per week and calculate the results. I'm trying to use COUNTIFS to get a result based on both the date I'm doing the analysis for and for the response. I've attached a sheet that shows a sample of what I'm trying to do. Cell B1 contains the date range I'm using, and cell B18 shows the formula I'm having trouble with. I suspect it's because I'm trying to treat a date like text, but I'm not sure.
View Replies!
View Related
COUNTIFS With Date Range
I need a COUNTIFS function (I think that will be the best function to use at least). I have a two columns of data, column one is dates (mm/dd/yyyy) and column two is states (AK, MA, MI, etc). I need a way to count how many times a states comes up for a certain month. For example, I need to know how many times MA appears through the dates 1/1/2007 and 1/31/2007. Here's what I have so far. It works but I can only specify one date. =COUNTIFS('SHEET NAME'!D:D, "1/5/2007", 'SHEET NAME'!E:E, "MA")
View Replies!
View Related
Converting COUNTIFS To 2003
I have the following excel formula: =LOWER(IF(($H2&":"&$M2&":"&$N2&":"&TEXT(COUNTIFS($H2:$H$2,$H2,$M2:$M$2,$M2,$N2:$N$2,$N2),"0000"))=": ::0000","",$H2&":"&$M2&":"&$N2&":"&TEXT(COUNTIFS($H2:$H$2,$H2,$M2:$M$2,$M2,$N2:$N$2,$N2),"0000"))) I need to convert the COUNTIFS into something that is Excel 2003 friendly.
View Replies!
View Related
Converting Countifs To 2003 Formula
I am having trouble converting the following Countifs formula developed in 2007 to work in 2003 Excel. Basically, I want to count the frequency of "N" in 'Data_Q2 2008'!$S$2:$S$9806, where 'Data_Q2 2008'!$B$2:$B$9806 = 'Metric Upload'!C2. 'Data_Q2 2008'!$B$2:$B$9806 is a list of physicians. 'Metric Upload'!C2 is a pick list for selecting the physican that is in the larger data set using Data Validation.
View Replies!
View Related
Multiple Countifs With Date Complication
I want to count the number of occurences involving 3 columns of a spreadsheet, but one of the columns is a date/time field, and I only want to analyse the date. Example: A B C John 1 02/07/2009 09:45:10 Peter 1 03/06/2009 10:14:12 Fred 1 Peter John 1 02/07/2009 09:45:10 How can I ask how many occurences of John are in column A, with a 1 in column B, with the date 02/07/2009?
View Replies!
View Related
Array Or Countifs For Counting Survey Responses
I have recorded the data from 12 records (i actually have hundreds, but im just using a small sample). Each record is represented by a vertical column. I want to find the total number of "x's" for only the records from Country1 (from all the answers). Then I want to find the number of "x's" from only Country2. I have tried the "countifs" function but cannot find a way to make it work. Would a different array function be better? How would I write it? P.S. The answer for Country1 should be: 15. The answer for Country2 should be: 5.
View Replies!
View Related
Multiple Criterieas Using Sumifs And Countifs Not Working
I am unable to get the correct results using Sumifs and Countifs function. Below is an example: If I use the formula as =IF(ISERROR(SUMIFS(F2:F8,B2:B8,"Africa",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"2B",E2:E8,"SY")/F9),"N/A",SUMIFS(F2:F8,B2:B8,"Africa",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"2B",E2:E8,"SY")/F9) I get the result as 0 but the result i want is 19.67% (1234 /6275). Since i have multiple criterias which might not be available at times in the data, i get the value as 0. BCDEF2CountryMain Conseq.Other conseq.Product Type Amount 3AustriaInternalNoneInt2B $ 278 4AustriaExternalNoneExt3C $ 900 5AustriaExternal8A $ 2,388 6NorwayInternalMV $ 567 7AfricaExternalSY $ 1,234 8AfricaInternalNoneIntJN $ 908 9TOTAL $ 6,275 Similarly i have used a formula using Countifs; =IF(iserror(countifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=3C")+counifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=2B")/F9),"N/A",countifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=3C")+counifs(E2:E8,B2:B8,"Austria",C2:C8,"External",D2:D8,"NoneExt",E2:E8,"=2B")/F9) And even the above formula does not work with the criterias given. What i require is, if the country is "Austria", "External", and "NoneExt" then count those lines which has product Type as "3C", 2B". So the result for the above should be 1. Request you to kindly let me know the correct way or the right formula to extract data for the above criterias.
View Replies!
View Related
Countifs Alternative On 2003 With Multiple Cells And Worksheets
I created the following in Excel 2007 but need to come up with a replacement formula in 2003. =COUNTIFS('Data High'!AM2:AM999,"0",'Data High'!AN2:AN999,"Non Corp",'Data High'!AL2:AL999,"No")+COUNTIFS('Data Medium'!AI2:AI2000,"No",'Data Medium'!AJ2:AJ2000,"0",'Data Medium'!AK2:AK2000,"Non Corp")+COUNTIFS('Data Low'!AA2:AA5000,"No",'Data Low'!AB2:AB5000,"0",'Data Low'!AC2:AC5000,"Non Corp") With this spreadsheet i pull data straight from a database and place it in to 3 separate worksheets. The first worksheet which is the summary sheet then updates all the totals of certain occurrences. The above formula is the same format for all fields in the summary with a few variations. But they all have the following in common. They draw data from all 3 worksheets and total it all up, and each countifs looks at 3 different cells. In terms of replacing it i have tried to use something like =SUMPRODUCT(('Data Low'!AA2:AA5000="No")*('Data Low'!AB2:AB5000="0")*('Data Low'!AC2:AC5000="Non Corp")) but don't seem to be getting it right.
View Replies!
View Related
Sum Function With More Than One Criteria
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 Replies!
View Related
If Function With Range And Two Criteria
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 Replies!
View Related
IF Function With Multiple Criteria
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 Replies!
View Related
Function Using Multiple Criteria
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 Replies!
View Related
Subtotal Function With Criteria
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 Replies!
View Related
Offset Function With Criteria
I would like a consistent OFFSET function formula across a row but my columns move from monthly to semiannual periods at a point in time. How do I handle the move from monthly to semiannual periods in my OFFSET formula?
View Replies!
View Related
Database Function Criteria Range
I am trying to use the function DGET() to reorganize 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 Replies!
View Related
COUNTIF Function With Multiple Criteria
I need to count rows that meet 2 criteria. I have seen this help page http://www.ozgrid.com/Excel/countif.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 Replies!
View Related
Average Function Based On Criteria
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 Replies!
View Related
Use IF/Lookup Function In Multiple Criteria
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 Replies!
View Related
Advanced Filter With Function As Criteria Range
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 Replies!
View Related
SUMPRODUCT Function With Date Calculation Criteria
I have a worksheet that contains investment security purchases, including purchase date, purchase amount and maturity date, each in its own row. I need to develop a formula that will sum the purchase amount based upon the term in days (maturity date  purchase date) of the investment. The respective rows do not contain the term in days so the formula will need to calculate the days to determine the aggregating criteria described below. One cell should contain the aggregate security purchase amount whose term is 190 days and another cell should contain the aggregate security purchase amount whose term is over 90 days. It seems that SUMPRODUCT with a condition is the best way to handle this; however, I cannot get the correct syntax of this formula.
View Replies!
View Related
User Defined Function For Summing Multiple Criteria
I am in need of creating a User Defined Function in Excel that will sum numeric values based on two text criteria. I have a large set of data in an Excel worksheet that includes a column for " Market Type" and a column for "Location ID". I would like to develop a User Defined Function that allows a new or inexperienced user of the template to use the User Defined Function to select first a "Market Type" then select a "Location ID" and get the sum of the amount those values represent. A SUMPRODUCT function in Excel works for this, but can be awkward or intimidating for a new or inexperienced user. For that reason, I am specifically looking for a User Defined Function that will simply the formula for them while at the same time maintaining the degree of accuracy I need.
View Replies!
View Related
IF Function Return Specific Criteria For True And Then For False
I need to check one column to see if it is less than another one If it is I need to have the IF function return specific criteria for true and then for false but then I also need it to rely on the results of another question from another column if the result of the first query is less than the figure in another column again I need to return specific criteria for tur and then for false An AND function requires both to be true but when one isnt what do I write?
View Replies!
View Related
Locate Date Function/Formula Based On Criteria
I am trying to find the starting effective month for a workyear. The criteria for the selected month is that the data BEFORE the effective month is all zeros. I manage to get the result if the data AFTER the effective month is ALL non zeroes. If there is any zero, the data fetch will be inaccurate due to the COUNTIF formula. Is there any other way or formula that will enable me to get the result. File attached for testing and reference
View Replies!
View Related
Sum Values That Meet Certain Criteria
I have a spreadsheet that is set out to look similar to a MS Project plan, with dates across the top, users in column A and Assignments in column B. In the corresponding cell that matches the user, date and assignment they will enter the number of hours they anticipate working on the assignment. I then have a summary sheet that covers all assignments, week ending dates and users. I want to be able to calculate the hours and copy it into the cell in the summary sheet that matches the Day, User and Assignment. I have tried using SUMIF, but this requires the criteria cells to all be in columns, but with this sheet the dates are in a row.
View Replies!
View Related
Spread Values When Criteria Met
I have worksheet where I would like to apply a number value proportionately throughout a column bases on userdefined conditions (i.e. >=, <=, Between) from other colunmns. See example below and attached worksheet with further detail. Col A Col B Col G Col L Col M 01380Employee 15.0 5.5 (0.8) 00304Employee 24.0  (0.8) 01901Employee 33.8 (1.0) (0.8) 01729Employee 43.0  (0.8) 01482Employee 53.0 (1.1) (0.8) 00299Employee 62.5   00069Employee 72.5 0.5  01783Employee 81.5   02076Employee 91.0   01934Employee 100.5   TOTAL 4.0 4.0 In the above example, I would like to have the TOTAL value of Column L (4.0) be inversely spread throughout column M based on criteria selected by a user in a section of the spreadsheet. Criteria: Column G >= 3 AND all values in Column L = 0; Spread 4.0 (Inverse of cell L12 = 4) in column M for records that match criteria. I would like to have a command button apply the logic of the conditional criteria set for Columns G & L and spread proportionately ithe value from n Column M. I added examples of the different criteria and the layout in the file attached.
View Replies!
View Related
Sum Unique Values Under Criteria
With reference to a thread that is nearly similar to my own problem: http://www.excelforum.com/excelgene...ml#post2076546 I have a similar problem, though maybe a little more advanced. I would like to do the exact same, but when looking at the sample.xls in the referenced thread, my data can have zero values/blank in the Ccolumn. For instance, if C6 is blank, then the formula will not add the value in the next row. In many cases this would easily be solved through sorting, but the structure of my sheet do not allow for this. So what I am looking for, is a formula for only regarding rows where the cells in column C is <>"". Maybe this cannot be done in a single formula, and has to be solved through VBA, but having seen some incredible solutions here, I thought I'd give it a try.
View Replies!
View Related
Repeating Criteria Between 2 Values Using MAX
I'm having trouble delivering a MAX value within a range. In column A I have a running list of numbers, in column B I have a yes or no answer and in column C I'm trying to write a formula that will deliver a MAX value from the A column inbetween occurancies of the Yes and No values from the B column. I'm trying the SUMIF function but it seems to be proving problematic. This is what it would look like. A B C 1 Y 1 2 Y 2 3 Y 3 2 Y 3 1 Y 3 2 N 2 7 N 7 6 N 7 5 N 7 3 N 7 2 N 7 6 Y 6 9 Y 9 3 Y 9 It is almost as if I'm trying to get the formula in the C column only find the latest MAX value from where it currently is to when the last changeover happened from Y to N. I can't find the Max dependent on a yes or no answer, but I'm struggling to make it only look at the last batch of Yes's or No's!
View Replies!
View Related
Values In Ranges As Criteria For Formatting
I have a spreadsheet where my dropdown menu is taken from a master list. The master list is also broken down into several catagory specific lists. I need a macro that will take the individual cells in the worksheet that use the master drop down list, check them against the contents of the specific lists (defined names) and assign a cell color fill based on wich list the data was found in. lets say my master list is: MASTERLIST apple orange bananna lemon pork chop sirloin chicken wings carrot potato onion sage thyme.............
View Replies!
View Related
