# occurrences

1. ### Query regarding RANK/COUNT based on occurrence

I have same data where I want to count how often a reference occurs, and then rank it by a specific stipulation. Below is my example. Column A Column B What I want column C to show ABC 00:00:00 1 ABC 00:04:00 2 BAC 00:00:00 1 BAC 00:03:50 3 BAC 00:02:03 2 So the formula should...
2. ### Scatter Plot X,Y Coordinate Number of Occurrences

I am looking to build a scatter plot to illustrate how many times an X,Y coordinate appears. So, if I have 15 occurrences of 2,3 then I would like to see 15 points at 2,3 on my chart. If I have 3 occurrences of 2,1 XY corrdinate, I want to see 3 points right there. I need this to be a scatter...
3. ### Average or occurrences help needed

I need to quickly evaluate pump rates. I have an output (pump gpm) that gets recorded to excel every minute in a new row. The pump turns on an off multiple times a day depending on build up. What I want is to know the number of occurrences a day that the pump turns on and the average gpm...
4. ### Formula merging a countif with a second if statement

Sorry for the vague description but not sure how best to describe this. Here is the situation below. I want column C to be a formula. Out of all of the occurrences of Oranges in column B, if any of them have a value of 5 or above in column A, then I want it to return a Y in column C for all...
5. ### Search for specific text within a cell that contains a lot of other text within array formula

Hello All, I have always appreciated the help that everyone has given here to other people and can usually find the answer that I'm looking for. You are all an amazing community. What I'm trying to do and I got most of it is find how many "occurrences" an employee has had within the past 30...
6. ### Counting the Number of Ocurrances

Hi I have a column that has a list of dates and words etc. What I would like to do is for Excel to Count the number of times the word "cancelled" appears. Watching you tube videos it only seems to show if its just one word, which is east. My criteria is CANCELLED on ROUTE CANCELLED on SITE...
7. ### Creating a tally chart

I am trying to create a tally chart. I have a spreadsheet with a drop down list containing roughly 30 different selections. When the same selection has been selected multiple times, I ideally want them tallied in a separate table on the same sheet. The top 5. I want the name of the selection...
8. ### Tracking Attendance

I seem to be a bit stuck and any help would be great. I am trying to track 2 items. The first is the number of times a person is sick or late to work. For that it was a simple: =SUM(COUNTIFS(D22:D29,{"SICK","LATE"})). Then I need to count how many different occurrences there were. In my company...
9. ### SUMIFs - simple example

Hi I've got a table where I want to add the sum of occurrences for two different letters (see below): A appears twice, B appears thrice and C appears 4 times. Assuming that this data covers two columns and 4 rows from cell A1, how would I write a SUMIFS formula that captures the number of...
10. ### Sum If

I am using Excel 2016 ( not 365 ) and have a good spec i7 CPU computer. I have a large spreadsheet containing 250000 rows and growing. Within the sheet one column contains dates in the format 01/01/2018 and another an alpha numerical value in the form AA1234. I am counting the number of unique...
11. ### Index/Match return column header (multiple occurrences)

Hi I am trying to lookup a value in Column A then look across that row to find a string and return the Column header. There may be multiple occurrences of the string in that row and I need to return all column headers where there is a match. The data I am looking up is on a closed workbook. I...
12. ### Search for instances of substring within array

Hello, I am trying to search a large list for the number of occurrences of a word located anywhere within a string, but can't figure out how to do so with countif and find. The list would look something like this but longer: (Searching for the number of occurrences of "apple") <tbody> A B...
13. ### Count occurrences of consecutive text

Hi, Is there a formula to count occurrences of consecutive text in a lengthy file, and put the number in an adjacent column, as in the sample below: <colgroup><col><col></colgroup><tbody> 019DAEA5-7213-4CA8-A421-C8E6696CFE10 3 019DAEA5-7213-4CA8-A421-C8E6696CFE10 3...
14. ### Help with VBA code to replace COUNTIFS formula that is crashing Excel

Hello everyone and thank you for the help. I am trying to copy this formula down a column to count certain occurrences and I need to copy it down for up to 400k rows: =COUNTIFS(\$O\$4:O4,O4,\$W\$4:W4,W4,\$AB\$4:AB4,AB4) Note the ranges are expanding as the formula is copied down. For whatever...
15. ### Charting/Graphing Data from 3 Pivot Tables

Hello.... I have 3 pivot tables all from the same source data. Pivot Table 1 shows all "occurrences". Pivot Table 2 is a subset of all "occurrences" that also have some specific "codes". Pivot Table 3 is a subset of Table 2 that counts only some of the "codes" listed in Table 2. I'd like to...
16. ### Count occurances between times on certain date

Hello All, I'm trying to do a formula that will pick out occurrences between 30 minute intervals, I also want this to be filtered by date however I'm struggling to compose a formula that can do this. So far I have this "=SUMPRODUCT((B2:B101<=C2)*(B2:B101>D2))" this will pick out the...
17. ### Having issues calculating number of occurrences of sicknesses on a worksheet (Excel 2003)

Hi I have found the forums here a great help in the past but never had to actually post anything for help, so I hope that someone will know a way to help me. The issue I have is that we track holidays and sicknesses at my company on one sheet, this has the dates along the top row, the staff's...
18. ### Count occurrences in a range

I have a column with a range of numbers ranging from 0 -200 (lets call this column a) I am trying to create a formula that allows me to count the number of occurrences between 1-25 Is this possible?
19. ### Count consecutive occurrences of only 1

Happy New Year to everyone Hello, I am looking VBA solution, which count the constant occurrences of 1 only Data got in cells C6:P22, Results shown in R6 AF22 Sample Data <b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px...
20. ### Counting occurrences based on 2 criteria, one being dates

Hi all, Trying to count the number of occurrences a value appears in a col. based on value and if it occurs in a specific month: Variables: A1 = apple A2 = July Data: A3 = apple, b3 = 07/01/2017 A4 = apple, b4 = 08/01/2017 Result should be 1. I've been trying CountiIfs with Month, but no...

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back