# Formula For Sum Frequency

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
ADVERTISEMENT
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
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
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
Dec 20, 2008

how can data sorted be more frequency as most frequent on top and as the following:

1

1

1

2

2

3

3

3

3

to

3

3

3....................

i attach file

View 3 Replies
View Related
Jan 31, 2006

I have a large data set. Within the column I have determined the mode. How

do I find the second most common number, third most common, etc.?

240

240

240

240

240

240

240

240

288

288

288

300

View 10 Replies
View Related
Mar 9, 2006

I have a grid of random numbers. What I want to do is make a new,

corresponding table that has those numbers' frequencies from the initial

dataset.

So, if I have a row 1,2,3,4,5, the first row in my frequency table shows the

frequencies for those numbers, whatever they might be regarding the entire

set of numbers.

I'm just wondering if there's a more automated way of doing this than

running the historgram function and then manually matching the frequencies

with the original numbers to come up with my table. My dataset is growing,

so that's becoming too time-consuming.

View 9 Replies
View Related
Sep 15, 2009

My data is on order database as follows:

column B: customer name

column H: order value

column C: Total Order Value for the customer calculated as =SUMIF($B$2:$B$36383,B2,$H$2:$H$36383)

column D: number of orders for that customer, calculated as =COUNTIF($B$2:$B$23155,B2), and defined as a range called No_of_Orders

I'm trying to work out how many customers fall into the various categories. So on sheet 2, I've got an array formula:

{=FREQUENCY(No_of_Orders,$F$4:$F$12)}, where F4:F12 contain the 'bin' values.

The trouble is, that gives me the number of orders that fall into each category, not the number of customers.

How can I get it calculate the number of customers?

View 9 Replies
View Related
May 29, 2008

I have large spreadsheet that analyses the number of times specific characters etc appear in individual cells, but only takes into consideration the filtered cells. My current formula that works fine is:

= sumproduct((subtotal(3,offset($c$66,row($c$66:$c$6959)-min(Row($C$66:$c$6959)),,1))),--isnumber(search("F",$s$66:$s$6959)))

So at the moment every time an F or other specified values is in one of the filtered cells it is counted and displayed in the respective cells. What I would like it to be able to do is recognise when a cell has say 3 F's, and increment the displayed product accordingly.

View 6 Replies
View Related
Apr 9, 2014

I need a formula that can lookup the sell price with the highest frequency. For example, I need to look up item 2100 in sheet2 and sheet1 contain data for vlookup like this:

ID No_Frequency Sell Price

2100 4 2.99

2100 5 3.49

2100 1 1.99

I need to be able to look up and return the sell price of 3.49 since it has the highest frequency.

View 4 Replies
View Related
May 5, 2014

In column A, I have dates.

In column C, I have shopping centre locations.

One or more sales promoters can be rostered at shopping centre locations performing full shifts and half shifts.

How can count the unique dates associated with the shopping centre using a Start date and an End date? Example below.

Col A Col B Col C Col D

02/03/2014 Joe Shopping Centre #1 D1=01/03/2014 (start date)

02/03/2014 Bob Shopping Centre #2 D2=10/03/2014 (end date)

02/03/2014 Lee Shopping Centre #1

07/03/2014 Tim Shopping Centre #1

07/03/2014 Kay Shopping Centre #3

12/03/2014 Joe Shopping Centre #1

The results for the Frequency formula should show:

Shopping Centre #1 - 2 (dates)

Shopping Centre #2 - 1 (date)

Shopping Centre #3 - 1 (date)

View 3 Replies
View Related
Mar 26, 2014

Sample Excel with Frequency.xls.Attached a sample sheet with some data. What I'm trying to do is combine the frequency function and add another variable. Not sure how to do this. I'm using Excel 2003, so I don't think I can use "countifs".

View 2 Replies
View Related
Mar 8, 2009

I am looking to find the frequency of times 1 number appears with another number in a row in a table

Click here for an example

or see the attached spreadsheet. I included the excel 2007 and 2003 versions.

View 6 Replies
View Related
Jan 3, 2012

I need to count the number of times a word appears in a column in a pivot table and add the total value in the next cell minus 1 if the number is more than 1.

MANAGER

(All)

QA

(All)

Count of 1ST LVL ERROR

Error Type

Count

[Code]...

The totals in C come from a pivot table created from the data in a separate sheet. I need to dynamically show who made an error and the frequency of that kind of error. I've gotten the Error Type count to display according to the word in column B, but column C will show how many times that a specific notation was made.

For example:

"Annualization" appears in column B 3 times, but column C shows that one of the descriptions is listed twice in the separate sheet. The current formula shows 3, but it needs to count the 3 in B, then add any number that is more than 1 minus 1 in the relative C cell to yield 4.

This is my count array: =SUM(LEN(B6:B4506)-LEN(SUBSTITUTE(B6:B4506,D6,"")))/LEN(D6)

I've also used: =COUNTIF(_1ST_LVL_ERROR,"*"&D6&"*")

I don't know how to get the second column to factor in, though.

View 1 Replies
View Related
Jan 9, 2013

I have a column of names and I want to find the frequency that each name occurs per row, but the cells have a string of text. For example:

Column A:

John Dimon, Matt Carpenter, James Chan, Gary Smith

Matt Carpenter, Leo Monroe, Craig Thomson

John Dimon, Matt Carpenter, Larry Barclay

I would like a way to count how many times each name occurs in the column. So John Dimon would be 2, Matt Carpenter would be 3. I tried countif but it doesn't seem to work with a string of text.

View 5 Replies
View Related
Feb 24, 2007

(1) i have a project of a series of numbers running left to right in a random order what i am after is to sort them into order

(2)i want to find the frequenzy on numbers and how often they apear with other numbers compairing one line to the rest all the way threw

View 9 Replies
View Related
Dec 27, 2008

I am using the formula below to count the unique items in column M. Two questions, how can I use the whole range M:M, but then also ignore the header because that would return 3. I tried repacing M2:M6 with M:M but I get #NUM!. I'm using 2003 but when I take this back to work I will be on 2007. If using the whole range M:M is not possible then I could just extend out to M2:M7000 which should cover the range.

PAS Sheet

LM1Count of PASPAS22FD093 FD094 FD645 FD646 FD64

Spreadsheet FormulasCellFormulaL2=SUM(IF(FREQUENCY(MATCH(M2:M6,M2:M6,0),MATCH(M2:M6,M2:M6,0))>0,1))

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

View 9 Replies
View Related
Jan 16, 2007

i need to write my own frequency function and after that make some modifications. here is the thing: i dont know how to run over ranges and i dont really know how to start.

FREQUENCY(data_array,bins_array)

Data_array is an array of or reference to a set of values for which you want to count frequencies. If data_array contains no values, FREQUENCY returns an array of zeros.

Bins_array is an array of or reference to intervals into which you want to group the values in data_array. If bins_array contains no values, FREQUENCY returns the number of elements in data_array.

View 6 Replies
View Related
Jun 29, 2007

how to make a frequency table and a bar chart for 6 different branches of industries. These categories have numbers (1 for branche A and 2 for Brance B and so on) I tried Tools > Data analysis > Histogram yet. But then the categories are displayed as numerators. I just want to show how many companies there are in Branche A, Branche B, etc. I tried to change the categories into text, but it wont make a difference. I will include the file.

View 3 Replies
View Related
Jul 28, 2007

I used Match array to count the frequency of occurences (ie. string begins ASC) with but to no avail. Match or Frequency function how to do this? =MATCH(IF(LEFT(A1:A9,3)="ASC",LEFT(A1:A9,3)),A1:A9,0)

Column A

ASC_8731

UNI_ST9821

ASC_6735

ASC_431

UNI_ST1421

ASC_1731

UNI_ST821

ASC_731

UNI_ST2330

View 4 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 11, 2008

I have four columns in my worksheet. In column C, I need to find the no. of times word "Alarm" is occuring. Further, with each "Alarm" entry in col C, there is a alarm type given in column D. I need to find the no. of alarms for each type and then display a bar chart for the alarm type vs. frequency of its occurence.

View 2 Replies
View Related
Mar 26, 2008

I have a spreadsheet that contains the number of faults in 12 hours. What I would like it to know the frequency of fault i.e 204 faults means a fault every 00:02:03

Is there a formula I can use.

View 4 Replies
View Related
May 21, 2014

It may be easier to view the attachment to see what I am trying to do.

I would like to split frequency data by groups.

So I have 2 different names in column A, with a bunch of their ratings in column C.

I would like to be able to view the frequency of their individual ratings to see who gave the most types of ratings.

View 3 Replies
View Related
Nov 11, 2009

I need to find the most frequent number for each name in a large datatable, eg. Name1=12.

ColumnA / ColumnB

Name1 / 12

Name1 / 11

Name1 / 12

Name2 / 100

Name2 / 105

Name2 / 105

Name2 / 98

Name3 / 14

Name3 / 14

Name3 / 2

NameX / ..

Since Pivot tabels cant do the trick as far as I know, i'm clueless of how to proceed.

View 10 Replies
View Related
Jul 4, 2013

I have a large frequency table, the dataset looks like:-

Category Age Frequency

A 1 4

A 2 3

A 3 2

B 7 1

B 8 3

C 4 2

C 6 4

I would like a formula to get:

A B C

1 7 4

1 8 4

1 8 6

1 8 6

2 6

2 6

2

3

3

View 3 Replies
View Related