Summing Groups

Apr 16, 2008

What 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, 2014

Product 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.

View 4 Replies View Related

Summing The Items Of Each Of Different Groups In A Table

Nov 24, 2009

Items, 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

View 10 Replies View Related

Sum Of Groups ..

Nov 13, 2008

I 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

View 9 Replies View Related

Groups In A Column

Dec 13, 2008

I 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.

View 7 Replies View Related

Formula - Age Groups

Mar 19, 2007

i 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

View 11 Replies View Related

How To Sum Groups Of Cells

Jan 23, 2013

I 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 Related

Comparing Value To Three Groups

Mar 2, 2007

Here'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?

View 9 Replies View Related

VBA For Repeating Groups

Apr 3, 2007

VBA 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 Related

All Possible Combinations From Groups

Nov 11, 2009

I 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 Related

Sort By Groups

Jan 14, 2010

I'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 Related

Formula To SUM Different Groups In Same Column?

Aug 22, 2014

Attached 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

View 6 Replies View Related

How To Align Groups Of Columns

Jun 3, 2014

I 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

View 2 Replies View Related

Formula To Find Age Groups

Nov 8, 2008

I 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 Related

Summarize Groups Of Values

Feb 4, 2009

I 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.

View 7 Replies View Related

Counting Groups In A List

Dec 12, 2012

I 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‎

View 4 Replies View Related

Permutations Of A List (groups Of 3)

Aug 31, 2007

Is 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.

View 13 Replies View Related

Groups Columns Automatically

Jan 28, 2010

Is there a macro which would group columns automatically

View 2 Replies View Related

How To Use Median On Groups Of Data

Oct 29, 2007

Attached 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.

View 8 Replies View Related

Subtotals In Groups Vs Filters

Dec 12, 2008

Subtotal 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 Related

Working With Groups Of Cells?

Feb 7, 2012

im 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.

View 1 Replies View Related

Adding Groups Of Number?

Apr 12, 2012

I 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]

View 5 Replies View Related

Divide Two Groups Of Cells?

Sep 21, 2012

I 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.

View 4 Replies View Related

Rank Only Groups With Same Characteristic?

Sep 5, 2013

I 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".

View 1 Replies View Related

VBA To Get Data Sorted Into Groups

Sep 20, 2013

Explaination 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] .........

View 1 Replies View Related

Creating Groups By Macro

Mar 3, 2014

I 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]....

View 9 Replies View Related

Spliting Up Cells Into Two Groups

May 3, 2007

to 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?

View 9 Replies View Related

Trying To Sort Groups Of Rows

Dec 31, 2007

Range("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

View 9 Replies View Related

Subtotal Groups Of Data

Feb 25, 2007

I 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.

View 9 Replies View Related

CheckBoxes Into Individual Groups

Jan 12, 2008

I 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







Copyrights 2005-15 www.BigResource.com, All rights reserved