frequency function

  1. N

    Sum if distinct value with multiple criteria

    I've been racking my brain all day on this one and desperately need someone to swoop in and give me the answer. Data is set up as follows: <tbody> SSN $Dollars Date 123 $10 10/10/2018 123 $10 10/12/2018 456 10/14/2018 456 $20 10/16/2018 123 11/10/2018 789 $10 11/12/2018 1011...
  2. R

    How to get last cell in a column in Array without VBA

    I have a column of data with varying length and I use the formula below to return a value. Is there a way to have excel determine the last cell in the column without using VBA? My assumption (I've tried a few times) is to use INDIRECT, but it keeps returning a numeric value (column is names)...
  3. P

    Unique record count with multiple criteria

    Hi This is me first time posting on this forum, so hoping someone can answer. I have data in the below sample format. I am trying to get a record count when the values in column 1(Week) and column2 (Name) match together. For example, User1 has three entries for 7/14/2018. So in column3, I...
  4. L

    Frequency formula.

    Hello All, I would need someones help with frequency formula. never used it and now i need it for some of my work, i can come up with basic one but not with one with multiple criteria. So here is what i need help on, i have a document with persons working times. Based on that i need to be able...
  5. H

    Help w Excel Challenge

    Hi friends, New here and really hope someone can help. This is for a work challenge so is super important to me. I have never created a macro in excel, though I'm almost positive this is the only way to get excel to perform the task. In my workbook I have price data for a stock by day (I am...
  6. B

    Counting using frequency formula with multiple criteria

    I have a financial modeling issue I can not solve. I am seeking: 1. The number of unique obligors with a senior debt principle value investments >0 2. The number of unique obligors with a principle value investment >0 <tbody> A,1 B C D What I expect the results to be: 2 Obligor...
  7. M

    Return value for every 5 positive numbers in a column !

    Hello guys, I am using this formula =SUMPRODUCT(--(FREQUENCY(IF(D3:D38>=5,ROW(D3:D38)),IF(D3:D38<=4,ROW(D3:D38)))=5)) which is an array to identify every 5 consecutive numbers above 5. The only problem is if there are more then 5 consecutive numbers and not exact 5 then it wont return...
  8. S

    Is there a way to count values that are of a certain criteria when they are in an array.

    Hi, I have this function, for simplicity's sake it is provided below: =FREQUENCY(IF(A1:J1<>"", COLUMN(A1:J1)),IF(A1:J1="",COLUMN(A1:J1))) Cells A1:Z1 are populated with a series of number with blank cells in between them, and the function above calculates how many cells are in a row until a...
  9. S

    Count Max Number of successive Data in Multiple Rows/Columns

    Hello, I am having issues calculating the maximum number of successive data in an array. I do have the formula for calculating the max number of successive data in a single row. However, my issue is I want to use that formula and apply it to rows below row 2 and beyond and have it return the...
  10. T

    Formula to Count Data in a Range That is Consecutively Outside of a Specification

    Hello all, I am trying to create a single formula in a single cell that lists the number of times an out of specification condition occurs for greater than two hours. As an example: Time Temp 0:00 -15 0:15 -15 0:30 -16 0:45 -16 1:00 -17 1:15 -18 1:30 -20 1:45 -20 2:00...
  11. K

    Sum costs from unique records having a given type

    <tbody> A B C D 1 Job# Description PO Type 2 3 5400 311 11st job 4 1101 NC Ward 350.00 deck 5 1102 IL Cotr flex 6 1101 NC Ward 350.00 deck 7 5204 600 Pat Place job 8 1102 IL Cotr 407.00 deck 9 1103 WA Brown flex 10 11...
  12. M

    Unique values in columns depending on other columns

    <tbody> Invoice # Order # Customer # Item # Item QTY 100001 1001 10 XB33 2 100335 1021 10 XB33 6 103009 1103 10 XB33 3 </tbody> Very small sample table. But let's say I wanted to add 2 columns to this. The first one would be labeled: Reorder? What I'm looking to...
  13. R

    Using frequency in a data validation formula to ensure unique entries

    Using frequency in a data validation formula for ensuring unique entries. Currently I use COUNTIF($B$2:$B$244,B2)<=1 or ISNA(VLOOKUP(A9,A$1:A8,1,FALSE)) to ensure all entries in particular columns are unique, but I have heard how slow these functions are and that the Frequency function...
  14. E

    Maybe if I use the frequency function?

    I have a data table that registers daily sales and there are several sales per day. I need to create an array function that groups sales into single days. </SPAN></SPAN> E.g.</SPAN></SPAN> Date Product Sales</SPAN></SPAN> 1/1/13 X...
  15. D

    Using frequency formula to find number of unique text occurrences

    Hi All, Had to know if the frequency array function can be applied for a text which we can by using the following formula "=SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))" which gives the unique occurrences of text in column A...
  16. J

    Search count of multiple values

    Hi, I need to know how many values in a column match my criteria: Column a:a 1 2 3 4 5 6 7 8 9 10 Column b:b S D T S S S D D D T I need a formula that will go through column b and count how many times the following values occured consecutively. S D answer: 2 S D T answer:1 D...
  17. A

    The number of duplicate data in two lists

    Hi, I am working on a workbook to help me schedule vacation in my department. One thing that I am having trouble with is getting excel to return the number of days that exist in two different lists. Example: WEEK 1 REQUEST1 5/13/2012 5/10/2012 5/14/2012 5/11/2012 5/15/2012 5/12/2012 5/16/2012...
  18. B

    Frequency Function with Times

    Hi, Struggling to get the Frequency Function to work with time. In Column B i have dd/mm/yyyy hh:mm this is exported from a Database. I want to know how many cases arrived between each hour of the day. I've used Text=(B2,"hh:mm") and also tried Text=(B2,"hh:mm:ss") to extract the time...

Some videos you may like

This Week's Hot Topics

Top