Summing Groups
Apr 16, 2008What I want is to sum any/all groups of numbers larger than 0 that are grouped in 4 or more....e.g. in my example D1:G1. The answer should be 150.... I'm trying this formula: ...
View 9 Replies
ADVERTISEMENT
VBA Code - Summing Groups Of Numbers
Jul 17, 2014Product Group Arg.Braz.Mex.Peru
Red paint Paint5324
Blue paintPaint5684
WallpaperWallpaper6585
LampsFurniture 4631
ChairsFurniture 1645
Green paintPaint1356
SofasFurniture 3358
SUM(Paint,Argentina)SUM(Paint,Brazil)SUM(Paint,Mexico)SUM(Paint,Peru)
SUM(Wallpaper,Argentina)SUM(Wallpaper,Brazil)SUM(Wallpaper,Mexico)SUM(Wallpaper,Peru)
SUM(Furniture,Argentina)SUM(Furniture,Brazil)SUM(Furniture,Mexico)SUM(Furniture,Peru)
I am trying to work out VBA code or another way in Excel to find a solution for this problem - I need to be able to sum up the values that I have posted below - so for SUM(Paint,Argentina), this would be the sum of the red paint, blue paint and green paint values under the Argentina heading, that are all categorised into the 'Paint' group. The same follows for all the other categories. I was trying to use 'Defined Names' to group the categories together, and so would then do =Sum(Paint). However this was not allowing me to sum by each country, and the 'Create from Selection' naming tool was not naming all of the values for Paint (blue paint, red paint, etc.) under the paint category.
This is an incredibly simplified version of the data I am actually dealing with - I am doing this for 70 country columns and 250 rows of product categories.
Summing The Items Of Each Of Different Groups In A Table
Nov 24, 2009Items, of 3 different groups, and their values are entered in a table progressively, over a year period . I am trying to work out a way of getting the month total value of each of the 3 groups seperately, for each month of the year. A sample worksheet is attached
Sum Of Groups ..
Nov 13, 2008I have a sheet of over 60000 rows a small section of which I attach..
I need a formula in cell d2 to give the total for c2 to c10,d3 to d10 will contain this same total...cell a11 starts the next group and the formula for the group total will be in d11 to d18 etc
Groups In A Column
Dec 13, 2008I have work sheet, ("Sheet1"), there are about 17000 plus entries in two columns A and B,. In Column C, following formula is inserted to show the "number of occurrences" of a Group in Column A.
=IF(ISBLANK(F4),"", IF(COUNTIF($F$2:$F$118,F4)=1, "", COUNTIF($F$2:$F$118,F4) & " occurances"))
Mine active worksheet is Sheet2. In Column A, an entry is made and Column B fetches the equivalent entry from Column Sheet1:B. The formula is :
=IF(ISNUMBER(MATCH(A1, Sheet1!$A:$A,0)), INDEX(Sheet1!$A:$C, MATCH(A1, Sheet1!$A:$A, 0), 2),
IF(ISNUMBER(MATCH(A1, Sheet1!$B:$B, 0)), INDEX(Sheet1!$A:$C, MATCH(A1, Sheet1!$B:$B, 0), 1),""))
When an entry will be made in Column A of Sheet 2, naturally it brings the equal of Column B. What I will like that in the subsequent columns, C,D,E,F,G,H, etc. the values may be reflected for all the occurences equal to entered in A1. For example if in Sheet 1, :
1234 xyzxyz
1234 bxyxdx
1234 ffffffff
1234 ggggg
So these are four occurences. in Sheet2, when one entry of 1234 will be made it will show any of the four values, the rest three values may be reflected in subsequent columns.
Formula - Age Groups
Mar 19, 2007i am setting up a work sheet on ages for a sporting team
i want to be able to type in the date of birth and have it automatically come up with there age then for it to go to an age group
eg
14-3-00 would go to 7 yrs then go to under 8's
How To Sum Groups Of Cells
Jan 23, 2013I am trying to sum groups of cells where the groups contain a variable number of cells (within a column). My sheet has the following columns, C = plot#, G = plant#(within the plot), and F = number of tillers(for each plant). I need to get the total number of tillers per plot. Plots have different number of plants, so for instance cells c11:c16 are plot 3, cells g11:g16 are number 1 thru 6 (plant#), so I need the total number of tillers which would be sum(f11:f16). But each plot has a different number of plants. I've been told I'm not the best at explaining things,
View 2 Replies View RelatedComparing Value To Three Groups
Mar 2, 2007Here's one:
Column C (starting at C6) contains the team number. I want column B to find out if the team number is an AM, Mid or PM team.
I have listed the AM teams at J1:U1, the Mid teams at J2:K2 and the PM teams at J3:V3.
How can the formula in column B search those ranges and determine if the team in column C is AM, Mid or PM?
VBA For Repeating Groups
Apr 3, 2007VBA code. My sales data is arranged as months in columns and customers in rows. I have about 36 months of data that must be formatted as a single date column for pivot table manipulation.
View 9 Replies View RelatedAll Possible Combinations From Groups
Nov 11, 2009I have a set of 13 groups, each with anywhere from 1-9 different variables for a total of 56 variables. I want to find all the possible combinations of 1-5 variables. Only one variable can be used from each group in a combination. Please see attached data sheet.
View 5 Replies View RelatedSort By Groups
Jan 14, 2010I've been trying to rank by groups within one list of data; I can sort by two columns and rank with a simple "if" statement, but this is no longer practical for how often the data is updated and re-sorted. Trying to find a dynamic formula that creates ranks by the groups in a selected column. Example attached.
View 5 Replies View RelatedFormula To SUM Different Groups In Same Column?
Aug 22, 2014Attached workbook is self explanatory but Cols B-C have matching data in groups with a value for each row in Col D.
I need a formula to SUM each group and input that SUM value in the adjacent Col/Row E
How To Align Groups Of Columns
Jun 3, 2014I would like to align a group of columns against another group of columns.
Columns G-L will not be changing. They have a certain amount of info I want to align with columns A-F
There are 30000 items in G-L and only around 3000 so far in A-F
Weekly I will import and reorder A-F and then want to realign them with G-L
This is a link to a sample of the original file: [URL] .....
This a link to how I wan the order to be : [URL] .....
As you can see there are many more rows in columns G-L
I will update columns A-F weekly and then want to realign G-L
Formula To Find Age Groups
Nov 8, 2008I have a column with customers ages in it. Now what I'd like to do is create a formula that checks all the age cells, then gives a percentage for each age group of my choosing. I tried some IF formulas but I just can't figure it out
View 4 Replies View RelatedSummarize Groups Of Values
Feb 4, 2009I have a spreadsheet containing Date, Description and Value in a three column setup.
I wish to generate one summary for each unique description, based on a table of types. I can then say that for instance "Apple", "Oranges", "Lemon" are all part of a "Fruit" group.
The lookup would then find every row containing words in this group and summarize the values for them all.
Counting Groups In A List
Dec 12, 2012I have attached a file to better describe my issue, essentially I would like to count all the 'A's, all the 'B's and so on in the file and have the count next to it, so something like
A 6
B 3
D 5
F 1
In the file I uploaded I have column B as a count of the numbers, is there a way I could get just the final number for each letter, that would solve my problem.
sample.xlsx‎
Permutations Of A List (groups Of 3)
Aug 31, 2007Is there any way to determine all possible unique combinations (or sets) of any 3 strings in a table? I need to make a master set list from a list of strings which could be over 100 elements long.
Once I have a result set, I need to get rid of any groups which have a value contained in another group.
Example:
cells A1 through B100 contain:
Item1 45
Item2 50
Item3 98
...
Item100 75
Result set would contain groups of any 3 item combinations where the corresponding "B" value is within a certain range (between 40 and 80), while also trying to create sets with the lowest possible sum (placed in the 4th column) of the 3 numbers.
One possible combination would be:
Item1 Item2 Item100 170
etc...
I need to list all possible combinations, without re-using an element from a previous (lower sum) combination that matched the criteria.
Groups Columns Automatically
Jan 28, 2010Is there a macro which would group columns automatically
View 2 Replies View RelatedHow To Use Median On Groups Of Data
Oct 29, 2007Attached is a spreadsheet showing various stockcodes in various districts with their prices. My aim is to find the median stock price of each stockcode which might show me the district which has a huge variance in price with the same stock. Average can be used on a subtotal, but median is not there. The spreadsheet is an example and the real list is thousands of rows, so I can't manually go and change the range for each stockcode encountered to find the median for each stockcode.
Any suggestions, I am using excel 2003 and can't download add-ins due to my work's network security, though I could do it at home if someone found a good add-in etc.
Subtotals In Groups Vs Filters
Dec 12, 2008Subtotal doesn't add cells hidden under a filter column but it does when grouping. How can I get groups to change a subtotal based on whether they are hidden or not. What I'm really trying to do is use conditional formatting to change the format when a group is expanded vs collapsed.
View 3 Replies View RelatedWorking With Groups Of Cells?
Feb 7, 2012im working with a number of related cells and am having trouble with entering formulas. at the moment im entering them in manually but im hoping theres a better way.
suppose I have a few groups of data. the first group is A1-C1, second group is D1-F1, etc. the values of these cells have to be output somewhere else. so if A1=dog, B1 = cat, C1=car then AA1 would be dog cat car. AB1 would be the concatenation of the next group.
Now my problem is how do i put a formula into AB1, AC1 etc. I cant drag and drop. If I drag AA1 to AB1, AB1 will be the concatenation of B1-D1 instead of D1-F1.
As an aside one thing I thought of is to just put the output cells further apart. So if the first output was to goto AA1 the next one could be output to AD1. The only reason I didnt do this is this sheet is wide enough anyway I didnt want to make it any wider. I also thought of outputting D1-F1 into AD1 and then copy/pasting those values into AB1 but that wouldnt work as I need these values to be changeable.
Adding Groups Of Number?
Apr 12, 2012I have two groups of data? I selected the cells in yellow and defined the name of those cells as group1. I want to be to do absolute value of individual cells and add them up. I tried pressing shift control and enter and it still isn't giving me a number. I can do it if the numbers are next to each other but if they are separated by a row it won't let me.
[URL]
Divide Two Groups Of Cells?
Sep 21, 2012I am trying to divide the total of two groups of cells and I used the following formula:
=sum(B3:M3)/(B17:M17)
However, that is not a correct formula.
Rank Only Groups With Same Characteristic?
Sep 5, 2013I would like to create a spreadsheet that creates kind of a leaderboard based on geography. To illustrate:
Tab 1:
Drop down menu with locations (e.g.: Texas Market, Arizona Market, California Market)Data flows into tab based on vlookup to menu (i.e.: Column B = revenues, Column C = cost of goods sold)More importantly, 5 Most Profitable Customers and 5 Least Profitable Customers
Tab 2:
Data in the following order (Customer Name, Market, Revenues, COGS, Gross Margin, Rank) - approximately 10,000 rows
Currently, I'm using a rather manual custom sort option to get the customers to line up in order of market, before using the RANK function in excel to sort by gross margin. However, I'm wondering if there's an "IF" statement of some sort where I can create a "RANK customer based on Gross Margin among peers with same Market".
VBA To Get Data Sorted Into Groups
Sep 20, 2013Explaination of Data:
I have 2 Columns in the "RawData" sheet in the file attached. One says the Campaign No, and the other says the Site Code. One Campaign, will always never have duplicate Site Code.
I want to organize the data in the following fashion (as seen in the "Format" Sheet). I wish to acquire the trends of grouping of Site Codes, meaning which site (by Site code) is sold together, to understand the hottest combinations.
The idea is to see which Site Code sells more with a particular Campaign.
As you can see in the attached file, I'd like to know how many times a particular Sites (by Site Code) was sold with the other. As you can imagine, I have about 300 Campaigns, but have about 1500 different Sites codes to deal with, this activity will save me hours of time.
The sample file is uploaded on Google Docs. [URL] .........
Creating Groups By Macro
Mar 3, 2014I am a new user into excel using macros how I can automate a worksheet, after I have about 300 rows as expenses of which I want some of these to distinguish groups. Note that the name of expense is not stable for each time which i download the file, so based on the name of the output, i would like to write a macro so that running this macro to enter a number in column B for each expense which i want to group it so that then using filter i can issue any group. Note after filtering i would like to have the total in column C. Please see below extract of spreadsheet.
Monthly expenses
Expense
Group
Amount
[Code]....
Spliting Up Cells Into Two Groups
May 3, 2007to set up a rule so say certain numbers can be put into two groups in another cell.
i.e. codes 01,02,03,04 are grouped in country 1 and 05,06,07,08 are from country 2, so in the same row in a seperate cell it would identify if the product was 1 or 2?
Trying To Sort Groups Of Rows
Dec 31, 2007Range("C"&((h-1)*6+2)"G"&((h-1)*6+7)).Select
What is the proper name convention if I want to sort a group of 6 rows at a time. For example if h =1 then I want to select from C2:G7 to sort but I am getting an error when I try to input it as above. I have the sorting code, just need it selected first.
Sorry, I don't know how to turn off smiley faces because the options list isn't available to me, but before the G is a : and a (
Trying to make it Range("C2:G7").Select
Subtotal Groups Of Data
Feb 25, 2007I have attached sample of a series of massive spreadsheets that I am working on.
Item Numbers in Column A
Flag in Column D
Values in Column P
I want put the total of the values in P for each group of item numbers in column C where the flag is YES.
Unfortuantely I prefer not to use excel's subtotal functionality or array formulaes here because of the logistics of how these are used.
CheckBoxes Into Individual Groups
Jan 12, 2008I have setup 3 “Control Toolbox, Checkboxes” on sheet "1." Each one puts specific information on sheet “MASTER” columns I, K, & M when checked. Columns J, L, & N on sheet "MASTER" are for dates. When Checkboxes 1(TT), 2(50%), & 3(70%) are checked, on sheet "1", the information is properly displayed. But, after checkbox 3 on sheet "1" is checked the date in column L on sheet "MASTER" is reset to “01/00/00.” And when Checkbox 3 on sheet "1" is unchecked the date in column L, sheet "MASTER," is cleared. I don’t know what I’m doing wrong in the code.
View 2 Replies View Related