Is it possible to get a unique count in a group subtotal though the elements in the group may not be unique using Pivot Tables w/o resorting to Power Pivot?
Are both members of NorthDivision
If someone is in Group 123 for 9 months, then Group 456 for 3 months, that membership should be reflected in the correct group accordingly.However, that person should only be counted once in the NorthDivision, not twice.
Group 123 0.75
Group 456 0.25
At first glance, it appears easy, however, what if membership is only total 3 months?Then Group would increment 0.25, but I need NorthDivision to increment 1
I need to count the number of unique companies that receive money within a specific QTR. I have made this simple example, I have a Table called Awards, with Headings for DATE, QTR, Company, Awarded, on one worksheet, that I need to feed the data into a summary on another worksheet. What formula using table heading can I use to achieve the answer 3 unique companies for Q1-13.
I have attached an example of what i am trying to do. I can work out how to calculate unique entries by putting in a formula under a pivot table, but is it possible to select an option in the pivot table settting which will give this result?
My objective is to count the number of "Horizontal-Horizontal" entries in Column D for a specific value in Column C, BUT (and this is where I am lost), taking into consideration only unique values in Column A.
So, for the highlighted red entry in my spreadsheet sample below, there are 2 entries of Horizontal-Horizontal for Column C value A2961. BUT since, Column A entries for A2961 are duplicates, I want to return a value of 1. Hope this makes sense.
This is my formula that is working for the first part of the equation. I need to add something to it to condition the count based on unique values in Column A.
I have an excel workbook which contains two sheets. Sheet 1 contains a list of items. I need to search for each of these items in sheet 2 and sum up the 'base price' and enter into column B of sheet 1. So basically
1. I need to pick up the first item in sheet 1. 2. Find the column which contains "Module Name" in sheet 2. 3. Search for the item picked up from sheet 1 in the column which contains "Module Name" in sheet 2. 4. Sum up the "Base Price" for all occurrences and enter this summed value into column B of sheet 1 next to each item.
I'm running the subtotal function, but for some reason Excel is including first row of next group in one of the groups. The label I am subtotalling on is the result of a formula, but why would that make a difference?
See row 11 and 12 in the attachment : subtotal problem.xlsm
I need to display a subtotal amount in a field named Invoice Total in my spreadsheet. I created a subtotal, which is the amount that I need on each line in the invoice total column. Here is an example:
(I've actually gotten what I needed through some very convoluted formulas, but I thought there MUST be an easier way!!)
I have three columns of data: foldername1TEST0001TEST0006 foldername1TEST0007TEST0008 foldername1TEST0009TEST0018 foldername1TEST0019TEST0021 foldername1TEST0022TEST0022 foldername1TEST0023TEST0028 another folderTEST0029TEST0031 another folderTEST0032TEST0039 another folderTEST0040TEST0056 yetanotherTEST0057TEST0058 yetanotherTEST0059TEST0101 yetanotherTEST0102TEST0104 yetanotherTEST0105TEST0106 yetanotherTEST0107TEST0154 yetanotherTEST0155TEST0190 foldername1TEST0191TEST0197 foldername1TEST0198TEST0267 foldername1TEST0268TEST0275
I am trying to get a "range" for each folder using the first instance in columnB and the last instance in columnC. For example, what I need to end up with is:
foldername1TEST0001TEST0028 another folderTEST0029TEST0056 yetanotherTEST0057TEST0190 foldername1TEST0191TEST0275
Again, I was able to finally accomplish this, but it took some way outside the box steps. Tried it in Access with simple query for Min and Max, but the caveat is that there are identical folder names for different ranges (see "foldername1) that need to be captured and I was getting results like "foldername1 TEST0001 TEST0275", which is incorrect.
Import Financial System -Recaps Trial BalanceFrom Date:31-Jul-2011,To Date:27-Aug-2011, Requested By:Jenny Drumm,30-Aug-2011 9:28 AMAge Range: 0 - 30, No. of recaps: 231 Broker File NumberAgeVendor/Co/DeptFirstBankAssistsFRTBRODTYLoadsInvInvClaimsGL AcctTRUETRUEBalanceCostFeeRecptsAdjRecptsROE VarLC Var
There are more age groups, & some groups have hundreds of lines. Each of the total line in the report has a number plugged in by the system, which, because of rounding, may be off by several cents. I figured out how to move the rows with Broker File Numer, etc & the row after it up above the first Age Range.
What I need to do next is:Delete all rows between "Age Range: ....." and the start of that range's data.Delete all blank rows between "Totals" and the next "Age Range: ...."Delete 5 rows after "Grand Total"Put a formula in the row immediately above each age range's "Totals" for each column, summing all the data for that column, in that age range. (Hopefully with the word Total in column B of that row, too)Put a formula in the row immediately above the "Totals" after "Grand Total" that adds together each of the Age Range totals.(I did figure out how to get the "Balance" cell in each row to sum that row going right to left, so at least I got a start! For that I'm using
LR = Range("C" & Rows.count).End(xlUp).Row With Range("R10:R" & LR) .NumberFormat = "#,##0.00_);[Red](#,##0.00)" .HorizontalAlignment = xlRight .VerticalAlignment = xlTop .FormulaR1C1 = "=IF(ISNUMBER(RC3), Sum(RC5:RC17), " & Chr(34) & Chr(34) & ")" End With
How do I enter a formula in excel 2010 that will give the total amount of times each number is in this group. Example : how many times (total) the number 12 showed up , how many times the number 27 showed, and so on for each number that is in the entire group of numbers, from 1 to 80 .
Here is the page I will copy and paste into a workbook sheet from the internet that i want to evaluate the times each number was called.
Very new to all this , I am a bit aged and need not to learn excel A to Z, just need to know what correct statements /formulas have to be entered to do what i desire.
I am only interested in the total count of the small bold numbers 1 to 80, each single digit 1 to 9 and double digits 10 to 80 will be in their separate cells. The large bold three digit numbers and dates/times will have to be erased (manually) before the calculation takes place.
I have a long list of products in an Excel column. I want to count the number of unique items in the list (the cells contain strings, not values).
I know I can use the "Remove Duplicates" tool in the Data menu, and then use a Count function on the resulting list. I wondered if there was an Excel function or formula that would do it, similar to the Frequency function that does the same thing with a list of values.
I realise this is beyond me and wonder if it is possible to list uniqure values in excel and place a count occurrences of each. I have a hugh list of over 400 alpha code i.e ABCD, ABCD, ABCE, BDCE, BDCF ZXYE etc etc. What I'd like to do is pick out all the unique values so I can see what alpha codes are on the list. But then I would also like to count the occurrences of each value/name so I know how many times they appeared on the list.
Col A ABCD ABCD ABCE BDCE BDCF ZXYE ZXYE YEZY CDEF
Col B is where i would like to list all the unquie vaules with a count e.g. ABCD 2 ABCE 1 BDCE 1 BDCF 1 ZXYE 2 YEZY 1 CDEF 1
I have a list of sales associates by items by manufacturer. I need to count how many manufacturers are represented by each associate. It might look something like this:
Smith, John Brand A Beer 12 oz Beer Industries, Inc. Smith, John Brand A Beer 8 oz Beer Industries, Inc. Smith, John Brand B Vodka 700ml Vodka Industries Jacobs, Sally Brand C Wine 1989 Wine Makers Co Jacobs, Sally Brand C Wine 1979 Wine Makers, Inc. Jacobs, Sally Brand D Wine 1908 Old Wine Makers, Ltd. Jacobs, Sally Brand E Beer Six Pack Fun Beer Co.
So the totals would be:
Smith, John Total # of Manufacturers: 2 Jacobs, Sally Total # of Manufacturers: 3
So the formula ignores the duplication in employee names and manufacturer names.
Is this possible? I'm thinking something like =sumproduct((Range=AssociateName)*(sum(1/countif(ManufacturerRange, ManufacturerRange)))
In Col X of the attachment, I have manually entered the count of the rows within each sub group, as determined by the counter in Col W. How can I do this automatically? I need this because when I filter the spread sheet by Rank, I need to know how many selections, of the filter Rank query, were in a sub group of ? number.
I am trying to come up with a formula that will count everything excluding 1 in one row, while looking at another row to determine the group. The attached example explains things a lot better.
I am going to have 2 formulas. 1 for the "Big" group and one for the "Small" The formula needs to look first at the column that has the group in it. Then it needs to count everything is column A excluding "Snake" And return the value.
I a formula in one of my cells so that when the data is filtered it will add up all the cells that have a yes in them. However I'm getting an error. If I use the countif formula it still adds all the cells with yes that are not in the filter selection.
I have a linear count from 1 to 160 (J3:FM3) and I hide columns manually over time depending on a certain criteria. However, I would like to count how many columns I have left. I believe you need to use the subtotal function, but I do not understand how to use it.