I'm hoping there's a more efficient way to do this in that I'd like to replace - for example - over 100 formulas with just 1.
Take this simple example range in a column of numbers: 100 down to 0 and for each cell there's a count of how many times that number is greater than those that follow. The 1st is 100 times, the 2nd 99 times and so on... finally I need a sum of these resulting counts.
My current method would require over 100 formulas in 100 extra cells to do this
in sheet 2 cell g16 i need it to count how many times "horse racing" appears in sheet 1 column m and also for it to count how many "y" are in column g of sheet 1 and return something like
4 from 4 this example is taken from the attached sheet
basically it needs to count the yesses in column g that correspond to the horse racing in column n
so there could be 7 instances of horse racing ( 4 in this example ) and count the y's in column g which could be 2 ( 4 in this example ).
I have data over the last 170 days. Each entry has a time stamp. I would like to count how many entries there are between 7:45 am and 2:45pm. A total count across all 170 days is needed, but it would be great if I could break that down by calendar day (i do have that as a column).
Here is a sample of the data:Friday11/15/1307:08AM
i know its out there, i just cant find it. I have a list of names in a specific pattern of cells on a spreadsheet. I would like excel to give me a number of how many names i have in this spreadsheet. I know COUNT does numbers, but is there a formula that counts names?
I have a range that contains a mixture of hard coded data and data pulled from a database sheet. I want to average only the hard coded data not the ones in the cells containing formula. I need this to be dynamic instead of just selecting the cells as formula might be added later on.
I am working on a sheet that has over 150 tabs of information. It is a simple audit that allows for Yes, No or N/A to be selected for each audit category. I have an Outcome Summary tab that is pulling totals for all the audit information on each tab. I did a CountA statement to count any cells (from all worksheets) that had anything selected, so I have my count for the amount of files audited, but for the life of me I cannot come up with a formula that counts the ones that passed or failed the audit. Needs a formula that counts for Yes as 1, No as 0, N/A as 1 (these don't count against us) and doesn't return an error for a tab that wasn't selected.
In a formula on another page I want to add together columns A and B, and then take the minimum of those additions.
So in the case above the minimum value I am after would be 3. This is because on R5 3+0 = 3 which is the lowest number of the additions.
I do not want to add a sum column in the source table and then take the minimum of that as I dont have great access to the source table.
Inside my formula I need to add them then using the min function grab the minimum value of them all.
I have tried many combinations of the MIN, SUM both in Array functions and not in array functions but cannot nail it. Getting the minimum of 1 column is easy - I am finding getting the minimum of the addition of two very difficult.
I should add that there can sometimes be 8 records, sometimes 10 records. So the formula needs to be written to ignore blank cells, but include cells containing 0.
The formula below was posted recently as a way to extract a numeric substring from somewhere in larger string. When I first saw this formula, how it "came up with the goods" and so I spent some time looking at parts of it to see what they did.
Although I've made some progress, I still don't fully understand it. Heres the formula, and below Ive detailed how far I've got:
1. The formula is returning the first substring of contiguous numeric characters from the full string in Cell A1 2. 9.99e + 307 ensures that the search value won't be exceeded by the returned value. 3. The Mid function uses Min(Find( to get the character position of the first numeric in A1 (so MID then has its first 2 parameters). So far so good. 4. Now... I cant work out where MID parameter 3 (the substring length) comes from. I suspect its the row function, but can't see how as this isn't an array formula, and without CTRL+Shift+ Enter, I could only get Row to return 1 in my experiments (giving me only the first substring digit).
How does the formula return the correct number of numeric characters, (apparently) without counting them?
I am stumped on a formula answer I am getting on a simple timesheet. The timesheet is set up with an In and Out column for each day and person for the entire month. A formula calculates their total hours worked on a daily basis, minus their lunch (30 minutes per day). So far, so good.
The trouble comes when I try to sum the hours worked for the entire month. I am getting an incorrect total. The example I am working I am summing E3:BN3, which show the employee working 15 days in a month, 12 net hours per day. 15x12=180 hours a month. My sum total is showing up as 300:00:00. It may help to know we use a 24 hour format. I have the results cell formatted as [h]:mm.
It works great if I want to count number of unique text values overall. However, I want to count the occurrences of unique text values if they meet specific criteria. (Like a countifs function would if it could count unique text).
I am trying to count how many work orders we have created from 3 months (90 days) ago. In an effort to make the input work minimal. I wanted to use the =Today() formula and then subtract 3 months.
So I currently have =Today() in A1 and then (A1-90) in cell B1 and I get the date I want.
However, When I use a countifs formula and my criteria is
1: Number of GOG 2: Number of JAM 3: Number of 2400s not including 2400s already in 2 and 1 4: Number of 2100s not including those already in 2 and 1 5: Number of VAC and FFF in 3 6: Number of VAC and FFF in 4
1: I have done =SUMPRODUCT(--(=Code="GOG"),--(Pass/Fail="pass"))
I have an excel sheet (2007) with over 800 records. Column A is having some names which are repetitive and Column B with some relative prj names they are alos repetative. I need a macro which counts all the names and Prj having unique combination and put in the another sheet of same workbook. e.g.
I wish to create a formula which counts the duplicates in the order which they appear from top to bottom. Thus the example below would count the first instance of ITEM_A as "1", directly below it the second instance of ITEM_A would be counted as "2" as it already has appeared once. Ex:
I have a report I deal with each day. The report has Apples, Oranges, Bananas, etc.... Sometimes the report only has 1 of the 10 and sometimes the report has all 10. How would I count the rows in this range to see how many line items I have? Letting me know if it's 1 or 5 or 10?
I have a spreadsheet which I use to count the occurrences of a value against a range. Basically if the value is uppercase I count as 1 and for lowercase 0.5
This spreadsheet is used as a holiday chart and I have previously used the formula below however, for some reason it no longer works. Not even if I change the lookup value.
The exact function takes a single cell as its value but I have a spreadsheet which allows a range. Although I cannot change it….
Refer to the attached sample workbook. I need to track daily tasks of our staff and then indicate these tasks on the "Activity Report" sheet.
Each task will have a code and I will indicate; by menas of the code, which activity was done by inserting that code in the time slot provided.
Once each day is completed; I need to tally up the amount of each activity that took place and it will be diplayed in the "Specific Activity Summary" section of the sheet.
Also; once the tally for each activity is completed, the amount of time spent on that activity will be indicated on the "Daily Activities Summary" sheet.
How do I go about linking the specific cells to be able to create this "tally" and "summary."
I have a pretty simple spreadsheet with two columns. In Column A is a unique identifier for a user (608901, 505141, etc.). In Column B is a date the user logged in (5/1/2014). For a week's span there are about 25k rows.
Users can and do log in multiple times per day creating duplicate entries for a single day (680717 might show up 3 times for 5/1/2014). What I need to figure out is how to get a count of all unique logins per day. So if 680717 in previous example logged in 80 times on 5/1/2014, that only counts as 1 unique login for 5/1/2014.
How to calculate a count of unique records across two columns (customer number and acct number) with out much luck. For example, a customer number may appear 3 times but be associated with 3 separate acct numbers.
I've found a lot of in-depth formulas online but I'm not sure if they're what I need. I think I need some sort of array formula but I'm a little stuck.
I have a series of numbers going down from max to min in column M, in column N I have the number of counts of the same numbers.
The Modal value is calculated by a formula and already displayed in cell F3 (it needs no modification).
What I am trying to do is this:
1) Identify the address of the same modal value in column M.
2) From the modal values address in column M, search Column N searching upwards and downwards from the modal value address, until the first instance of the pre-specified count number is identified.
3) When the pre-specified count number is identified look beside in the cell in column M and display the value beside that pre-specified count in cell.
We stop at the first instance of that prespecified count both when 1) counting upwards and 2) counting downwards.
The upper value is displayed in F2 and the lower value is displayed in F4.
An example would be.
1 1 2 2 3 3 4 4 5 6 6 1 7 2 8 3 9 4 10 1
The modal value is 5 looking beside it as it has 6 counts, so in the count column we search upwards from the count of 6 until we encounter the pre-specified number of 4, then we look beside the count of 4 and observe that the number 4 above the modal value has 4 counts hence we display the number, now we search downwards from 6 counts until we reach the first instance of 4 we find it and the cell beside it contains the number 9 so we display the number 9.
The end result is numbers 4 and 9 that have 4 counts each.
I have data created by a program where it gives me a column "Code" with different text categories (in this case names of people). One person appears multiple times in this column as it track each time they register a statistic for the sporting event. The statistics are text fields registered under the column Labels
So I have two columns titled "Codes" and "Labels"
I need to extrapolate from this data a count of times each statistic happens for a specific player.
Example: Code: Labels: John FGA John FGM Joe FGA Joe OREB Zach TO
What I need is broken down by player with counts of each category FGA, FGM, OREB ect specific for each player creating a box score for the event.
I encounter some problem in excel. I would like to get the counts that satisfy two more conditions. For example, I want the count that is both "m" in column F and "1 yes" in column G. Do I use COUNTIF or some other functions? i try COUNTIF it doesnt work well, the counts obtained doest satisfy both conditions.
