FREQUENCY Formula With Multiple Criteria
Jan 10, 2008
I hope someone can help me with adding an additional criterion to the formula below, which works and provides an accurate answer. The array formula is:
{SUM(IF(FREQUENCY(IF('Sheet1'!$O$5:$O$315="",IF(Sheet1'!$B$5:$B$315"",MATCH("~"&'Sheet1'!$B$5:$B$315,'Sheet1'!$B$5:$B$315&"",0))),ROW('Sheet1'!$B$5:$B$315)-ROW('Sheet1'!$B$5)+1),1))}
Where Column O has either a date or is blank.
Where Column B has a unique ID number which occurs more than once.
What I need to add is one more criteria from Sheet1,
Where Column P has a unique names which occurs more than once.
I tried two changes:
1. Adding an additional IF statement as follows: SUM(IF(FREQUENCY(IF('Sheet1'!$P$5:$P$315=A$8,IF('Sheet1'!$O$5:$O$315="", ... Which I couldn't get to take; and
2. Adding a IF AND statement as follows: SUM(IF(FREQUENCY(IF(AND('Sheet1'!$P$5:$P$315=A$8,'Sheet1'!$O$5:$O$315=""), ... Which took but gives me a #VALUE error.
View 9 Replies
ADVERTISEMENT
Jan 5, 2009
I have a list of 267 individuals who are each in one of 4 class designations (A,B,C, & D). They produce widgets for me. The individuals’ names are in A2:A268, their class designation is in B2:B268; and the number of widgets they each produced is in C2:C268. Row 1 is a header row.
I used the FREQUENCY function, for the first time, to show how many of the individuals produced 1-10, 11-20, 21-30, 31-40, 41-50, 51-60, and 60+ widgets last year. NOW, I’d like to take it a step further and show the number of individuals that produced widgets in each of these number groupings, but want it further split out by their A, B, C, D, class designation. So, I want to see how many individuals in the “A” class that produced 1-10, 11-20, 21-30, etc. widgets; and how many individuals in the “B” class produced those numbers, etc.
I’ve been playing around with using the COUNT and IF functions together and the FREQUENCY and IF functions, but have not been successful. Am I making this more complicated than it is? By the way, their class designation doesn’t have any relation to the number of widgets they produced, and the data is sorted alphabetically by the individuals’ names.
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
Mar 6, 2008
I've tried to adapt a formula I've found in the forums. But the result comes back at a figure much lower than I expect.
I have a column of numbers (column J)that I would like the frequency of but only if the date contained in another column (column R) is beyond a certain date.
Columns J & R do contain blank cells.
This is my adaption: -
=COUNT(1/FREQUENCY(
IF('Current Bonds'!$R$13:$R$490>39173,
IF('Current Bonds'!$J$13:$J$490>0,'Current Bonds'!$J$13:$J$490)),
IF('Current Bonds'!$R$13:$R$490>39173,
IF('Current Bonds'!$J$13:$J$490>0,'Current Bonds'!$J$13:$J$490))))
View 8 Replies
View Related
Mar 26, 2013
I worked out the formula below to count if frequency. However, now I need to work it out for 2 criterias.
Count if I:I = A2 and if R:R = Closed
What I have so far is this:
{=COUNT(IF(FREQUENCY(IF(ticketInformation!I:I=A2,ticketInformation!B:B),ticketInformation!B:B),1))}
View 2 Replies
View Related
Jul 10, 2007
I have a column of race results in A:A, ie (1,2,3,4,5 etc placings) and need to count the highest frequency of 'outs' not wins (wins being #1 ) between wins, and also the highest frequency of wins in succession.
View 3 Replies
View Related
Feb 25, 2013
I need to get a general formula which if possible, automatically calculates the frequency of unique texts (no duplicates) under a particular month (January) without manually inserting the cells which is under january. If possible, the formula will also check to see if the company is either Cash or Cheque.
I have three columns; Date, Company name and Payment mode.
Date ranges from 1 jan 2010 to 31 dec 2012. Cell A2 to A234
View 1 Replies
View Related
Apr 14, 2014
I have ID in column A and amounts in column B. ID's are duplicated, and I need to add amounts based on ID, so for example total of ID 156 should be $13,851.14 or 156 should be 7,290.63.
In the following example ID are in order, I need a formula that adds up the amounts in column C.
152
(14,542.81)
152
(199.25)
153
(37,554.98)
153
(19.92)
[Code ...........
View 5 Replies
View Related
Feb 9, 2014
I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
View 12 Replies
View Related
Feb 13, 2007
im using this formula =FREQUENCY(Sheet1!B1:B100,A2:A11) to calculate the the frequency of my data from B1 to B100. I would like to know how i can insert the Index formula into the formula above so i can get the frequency of the first 100 rows even if i add rows.
View 2 Replies
View Related
Feb 12, 2010
I am using the below formula to distinctly count the number of customers that match the criteria that I have in Cells C7 and B10. The data is in a separate worksheet, that I am showing Named Detail of which will be changing on a monthly basis, so a pivot table does not want to be used. The detail data ranges from row 7-40,000, and the file is currently 8610KB's, and can potentially grow.
=SUM(IF(FREQUENCY(IF(Detail!$A$7:$A$40000=C$7,IF(Detail!$B$7:$B$40000=$B10,IF(Detail!$D$7:$D$40000<> "",MATCH(Detail!$D$7:$D$40000,Detail!$D$7:$D$40000,0)))),ROW(Cust)-MIN(ROW(Detail!$D$7:$D$40000))+1),1))
This formula works but takes an excessive amount of time for one caluclation, and I need this for multiple column and row critera. So, can this calculation be changed in order to get the same result with faster calculation time? I am using Excel 2003.
View 6 Replies
View Related
Jun 21, 2013
I use the following CSA formula to give me the total highest intervals of zeros in a range. I understand how most of it works apart from one thing and that's how the data array and bins array give me the result.
=MAX(FREQUENCY(IF(H2:Y2=0,COLUMN(H2:Y2)),IF(H2:Y2>0,COLUMN(H2:Y2))))
So this is what I understand:
H2:Y2=0 - Returns TRUE/FALSE in the array based on range having 0 or a value other than that - ie{TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,TRUE}
COLUMN(H2:Y2) - Returns the column number {8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25}
IF(H2:Y2=0,COLUMN(H2:Y2)) -Returns the column number for every TRUE value
IF(H2:Y2>0,COLUMN(H2:Y2)) - This does the same as the above steps but returns column numbers for every cell that contains a value greater than 0
MAX - Returns the biggest number within the array ie =MAX({3;0;0;0;0;0;9}) would return 9
What I need to know is how the FREQUENCY formula gives {3;0;0;0;0;0;9}
View 3 Replies
View Related
Mar 11, 2014
Using frequency in a data validation formula for ensuring unique entries.
Currently I use COUNTIF($B$2:$B$244,B2)
View 4 Replies
View Related
Jan 16, 2014
I need to create a formula that takes into consideration 9 possible scenarios using IF and AND. I have 3 performance measures (exceeds requirements, meets requirements, improvement needed) and 3 potential measures (low, medium, and high). I have a spreadsheet where each individual is rated for both. Each combination correlates to a numeric rating on a 9 box grid. I need to include every option in my formula so the correct rating is determined for each individual. I've tried several versions on my own, and can't get past one set of conditions.
Performance PotentialRating
Exceeds ExpectationsLow4
Exceeds ExpectationsMedium7
Exceeds ExpectationsHigh9
Meets Expectations Low2
Meets Expectations Medium5
Meets Expectations High8
Improvement NeededLow1
Improvement NeededMedium3
Improvement NeededHigh6
Unacceptable Low1
Unacceptable Medium3
Unacceptable High6
View 3 Replies
View Related
Aug 17, 2007
I'm looking for a formula which will filter the area expiring in any given year per property. An example would be (from the attachment) - what percentage of area of Property 2 is expiring in 2010? Answer is 50.94%.
View 2 Replies
View Related
May 12, 2014
I am trying to modify an existing nested vlookup formula to include one more condition. I attached the excel data file. There are two tabs:
Tab #1 - Performance
Column B (Email Send Date): can be a repetitive date, something like
Row 2. 4/25/2014
Row 3. 4/25/2014
Row 4. 4/25/2014
Row 5. 4/25/2014
Row 6. 5/2/2014
Row 7. 5/2/2014
Row 8. 5/2/2014
Row 9. 5/9/2014
Row 10. 5/9/2014
Row 11. 5/9/2014
Column F (Product ID): can be same product for different Email Send Date. For instance, Row 2 & Row 9 have the same product ID - 128 and Row 5 & Row 10 have same product ID - 131.
Row 2. 128
Row 3. 129
Row 4. 130
Row 5. 131
Row 6. 567
Row 7. 897
Row 8. 987
Row 9. 128
Row 10. 131
Row 11. 234
Column R: Units Sold - need to retrieve the units sold value from Column D - Units Sold in UnitsSoldOnlineVlookup tab.
The formula needs to lookup the Units Sold from a table in a different tab, named UnitsSoldOnlineVlookup. This table contains the following columns:
Tab #2 - UnitsSoldOnlineVlookup
Column A - Email Send Date
Column B - Product Description
Column C - Product ID
Column D - Units Sold
Before Product IDs were different for each Email Send Date and I successfully used this formula:
=IF(ISERROR(VLOOKUP(F2,UnitsSoldOnlineVlookup!$C$2:$D$31000,2,FALSE)),0,
VLOOKUP(F2,UnitsSoldOnlineVlookup!$C$2:$D$31000,2,FALSE))
Now I need to embed one more condition to this formula - lookup Units Sold for the Product ID as well as the email date:
lookup Units Sold for a Product ID for a corresponding Email Send Date in UnitsSoldOnlineVlookup table and return Units Sold into the corresponding cell in the Performance tab.
I thought to use MATCH function in addition to IF and ISERROR functions but I it doesn't work - I know it is wrong.
=IF(MATCH(B2,UnitsSoldOnlineVlookup!$A$2:$D$31000,0),
ISERROR(VLOOKUP(F2,UnitsSoldOnlineVlookup!$C$2:$D$31000,2,FALSE)),0,
VLOOKUP(F2,UnitsSoldOnlineVlookup!$C$2:$D$31000,2,FALSE))
View 7 Replies
View Related
Jan 28, 2014
I am needing a formula to sum info from 1 book to another based on two lookup criteria. Its the sort of info you can easily get with pivot tables or filters but my colleague needs a copy and pastable formula so it can be automated.
There are two workbooks Order Index example.xlsxCash Flow book example.xlsx; one called "Order Index" has a list of orders placed along with supplier name (Col C), date due for payment (Col H) and value of order (Col I). The other workbook, used by a colleague is called "Cash Flow" and contains a list of supplier names (Col B) with row 1 containing months (1st Jan 2014, 1st Feb 2014 etc).
Under each month on the "Cash Flow", I need to show the sum of orders due for payment within that month, for each supplier named (in Col B). In other words, sum the figures from the "Order Index" based on supplier name and date due for payment.
Often, on the Order Index there is more than one order per month from a supplier and the dates due are specific days (10th Jan, 21st Jan etc) and these need summing up for the 1st Jan 2014 Column in the Cash Flow book.
View 1 Replies
View Related
Oct 1, 2009
I have 2 columns of data being populated by vlookups
Column H is both numbers and text. Column I is Text and blanks. I need to be able to find only numeric values in column H greater than 0 and compare those occurrences with the corresponding cells in column I and if column I has a text entry (not a blank space) than to count that and at the end give me a total number of times these 2 criteria are met. As an example.
If column H has a text entry then don't count it.
If column H has a number less than zero then don't count it.
If column H has a number greater than zero but column I is blank then don't count it.
If column H has a number greater than 0 and column I has a text entry then count it.
I've tried using many variations of a sumproduct formula and none of them work.
This formula counts all instances where column I has a text entry without checking column H for a number greater than 0.
=SUMPRODUCT(--(H2:H110>0),--(I2:I110<>" "))
Or it's possible that the formula is counting the text entries in column H as a number greater than 0 but I'v tried excluding text using this..
=SUMPRODUCT(--(H2:H110>0&<>"*"),--(I2:I110<>" "))
but this causes an error in the formula somehow that I can't figure out. I even tried this
=SUMPRODUCT(--(H2:H110>0&"*"),--(I2:I110<>" "))
and I get a formula that counts only the times text appears in column H and column I together which is not what I want either.
I'm self-taught on Excel so I know there's a lot I'm not understanding about creating formulas like this but I need to have this working by Friday and I just want it to work.
View 8 Replies
View Related
Jan 27, 2010
Can anyone tell me what is wrong with the formula on page 1 cell F56? it is not adding the 666 gallons at the end of column F for 426 - cc. Any ideas?
View 11 Replies
View Related
Nov 15, 2013
formula stating that:
if a number is greater than 1 add one 1 box (250) and if the number is greater than 9 add 2 boxes and is its greater than 18 add 4 boxes
i want this all in one formula,
View 1 Replies
View Related
Dec 6, 2012
How could I add ">0" to the below formula array?
{=IFERROR(AVERAGE(IF(Reports!$A$1:$A$4000=A5,Reports!$H$1:$H$4000/86400)),"-")}
Reports!$A$1:$A4000 = column A lists multiple agent's names
A5 = a specific agent's name
Reports!$H$1:$H$4000 = column H contains a number which has been converted from time to a number
86400 = number of seconds in a day (the cell where I am placing the formula converts the number back to a time format)
The average I'm looking for will give me an average of all times which are greater than zero.
View 3 Replies
View Related
Mar 10, 2014
Attempting to calculate assignment hours based on three fields -
A - Date
B - Txt
c- Txt
D - Time [Start]
E - Time [END]
F - Number [Off]
G - Number [Travel]
H- Number (Calculated Total Project Hours)
I HAVE THE CALCULATION WORKING WITH THIS FORMULA
=(E5-D5)*1440/60-F5+G5
My goal [If Activity is NOT NULL and Start Time is NULL] Time would Default to 8.0 - ELSE use the formula to calculate [ Some projects I don't have a set start/end - and default to 8.0 hrs.
View 2 Replies
View Related
Apr 29, 2013
Frequency
Formula to show the final/total a numeric values appears in an adjacent column.
I am currently attempting (I've researched many posts on this), to count /show the final totals a duplicate numeric value appears in one column, in an adjacent column (example below).
I am currently using an array Frequency formula below (courtesy of the board) filled down in column Q. this works well to count the frequency of duplicate numeric values appear on the list.
Formula used in Column Q below
{=IF(C2>=0.1,MAX(FREQUENCY(IF(C2:$C$1000=C2,ROW(C4:$C$1000)),IF(C2:$C$1000C4,ROW(C2:$C$1000),""))))}
However my aim is also to show the final entry of a numeric value in adjacent column R, by displaying text to indicate this. My example below shows “final” in column R
NB: My list is sorted by column C to ensure all numeric values are in ascending order.
Example
Column C Column QColumn R
12567 1 final
15789 1
15789 2
15789 3 final
23456 1
23456 2 final
12678 1 final
18965 1
18965 2 final
View 5 Replies
View Related
May 19, 2014
I am looking for a formula that averages the numbers in an array if they match the row and column text-based criteria. Based on another thread, I found and edited the following formula. However, it is giving me incorrect numbers.
[=AVERAGE(IF(($A$3:$A$275=$P6)*($B$2:$M$2=Q$4),$B$3:$M$275))]
I have attached a sample workbook that includes the broken formula.
Average Formula Error.xlsx‎
View 4 Replies
View Related
Jul 9, 2014
I need an array formula that can take average based on multiple criteria present row wise and column wise.
Check the attached sheet for detail. I need array formulae in yellow space. I have given one result that I need.
Average.xlsx‎
View 13 Replies
View Related
Dec 3, 2013
In the attached I have a pricing list on sheet 2 based on various criteria and on sheet 1 dropdown lists to match the criteria.
In cell B12 i have an index match that I can not get to return a value.
Is Index Match the correct method of returning a result? Or have I just got the formula wrong?
View 1 Replies
View Related
Dec 7, 2013
What I require : Column F to be blank as long as the following is true:
1) Column A (Order) cannot be blank.
2) Column E (Status) has to be equal to either "Completed" or "Successful".
3) Column D (Sales) has to have the same date as Column C (Contact), but due to bad data quality each order number can have several rows and as long as the Sale date from one of the other "Duplicate" rows equals the contact date it needs to be counted. This is as long as there is not already a row for the Order that already has Sales / Contact day matching.
In the attached spreadsheet, I have highlighted the rows that should be blank in column F in green. My attempts at formulas to count this are in the columns highlighted in red.
WORKAROUND.xls‎
View 1 Replies
View Related
Jun 17, 2014
Fixed data labels over a number of columns and i want to count the number of times 2 of these appear however when ive tried to use the countifs function it will only count if in the first colum of the range selected and for example if A2 says 'FRANCE' and D1 'RED'
Ie i have been using COUNTIFS('Datasheet'!BB:BE,A1,'datasheet'!BE:BH,D1)
View 3 Replies
View Related
Feb 11, 2010
In the attached vacation accrual spreadsheet, I created two tables: Exempt hours earned, and also, Non Exempt hours earned. I have all employees on one worksheet. The hours accrued (Vacation Level) are different for Exempt and Non Exempt. I need: What is the best formula to autofill the Vacation Level column (H):
It needs to choose exempt or non exempt from column (C) then reference the # Years of Service column (F) to find the correct vacation days earned as defined in the two tables on the right, or those numbers could be used in the formula itself. . . I JUST DON'T KNOW! Nothing I am trying is working!
create a formula! Use the spreadsheet to maybe make name ranges? nested IF statements, or LOOKUP, or even VLOOKUP??? Or something that works. not to repeat, but. . . Nothing I am trying is working!
View 3 Replies
View Related
Feb 5, 2013
Is it possible to use an Index/Match formula that looks at multiple criteria?
View 1 Replies
View Related
