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


ADVERTISEMENT

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

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

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

How To Multiply Groups Of Cells Using SUM Function

Mar 21, 2014

I cant seem to multiply two groups of cells at the same time. Its a hard one to explain so this is what I have tried to use and it doesn't work

=sum(a1:a5*5),(b1:b5*10)
so I tried...
=sum((a1:a5*5)(b1:b5*10)) and that didn't work either

what I want to do is add a1-a5 then * 5 and add it to the total of b1-b5*10.

View 8 Replies View Related

Auto Separating Groups Of Like Cells

Oct 8, 2008

I am working on this project for work that is pretty large (about 13500 rows) that is filled with items that I currently have sorted alphebetically. What I need to do is if there is a group of cells that have the same code, I need to insert a row at the very end of that group and autosum the quantity. It is becoming very time consuming doing it all by hand so I am trying to find out: is there a function that will automatically insert a row after each group of codes?

View 2 Replies View Related

Assign Cells To Groups (ie IF Statement) ...

Nov 11, 2009

I have been given a spreadsheet with the sales data for each of our salesmen. Each job has its state listed and what I need to do is break down the information by region. The catch is that the regions are not given - just the states. So I need to assign the states to their corresponding regions. To do this, I tried writing an "IF" statement like this:

=IF((B2="AL"),"SOUTHEAST",IF((B2="CA"),"WEST",IF((B2="NJ"),"NORTHEAST",IF((B2="TX"),"SOUTH CENTRAL", ... and so on.

So I was doing this for all 50 states. The problem is that the statement got too long. So how do I write this? There has to be a shorter/easier way.

View 8 Replies View Related

How To Add 2 Groups Of Cells And Divide Number By 60

Feb 6, 2014

I am trying to add 2 groups of cells and then divide the number by 60?

I can't get it to divide? It will only total.

=SUM(T807:T832)+SUM(T837:T846)/60

View 2 Replies View Related

Search For Groups Of Consecutively Appearing Cells?

Dec 7, 2012

I have uploaded a sample sheet to illustrate what I am looking to do.

I want to pull out all instances where the four sheets of a 'book' are the same and appear in the same order. (column b is not really important) I have thousands of 'books' in the sheet so doing the task manually is not really an option.

View 2 Replies View Related

Matching A Cell To One Of Four Named Groups Of Cells

Aug 11, 2008

I am trying to write a formula that will return a result based on which list a cell is found in.

Column A comprises of aisle/position information. The values range from B01 to B65, C01 to C65 ... H01 to H65 (Like aisles/shelf positions in a supermarket). This is not a static list, as the information in column A will change from week to week. It will be sorted prior to trying to work with it. The first three characters of the values in A will be what I'm comparing to the list. There is a fourth character that determines further details about the item that are not pertinent to this issue.

I need to match the value in column A to one of the four groups of values below and have the correct color display in column F. The correct color would be the name of the list that the value is found in.

Blue (K2:K61) contains the values B01 through B25 and C01 through C25.

Red (L2:L80) contains the values F01 to F21, G01 to G23 and H01 to H35.

Yellow (M2:M95) contains the values B36 to B65, C45 to C65 and D23 to D65.

Green (N2:N124) contains the values E36 to E65, F34 to F65, G34 to G65 and H37 to H65.


It probably boils down to a nested IF statement that checks to see if LEFT(A2,3) is found in any of the four lists, but I just can't wrap my head around how to write it.

View 9 Replies View Related

How To Count Groups Of Cells In A Range Using Specific Criteria

Jul 24, 2014

I want to count the number of groups of 3 adjacent cells in a horizontal range (C5:EV5) in which any cell in the group of 3 has a value in it (as opposed to all the cells being blank or containing "0"). For example, if any or all of the cells in C5:E5 have a value in it, it would count as 1, and if any of the cells in the group F5:H5 have a value, it would also count as 1, and so on. Is there a way to use COUNTIF for this?

View 5 Replies View Related

Use Combobox ListFillRange To List Values From Two Groups Of Cells?

Feb 24, 2010

Is it possible to use the Combobox ListFillRange to list values from two groups of cells? For example, in column 'A', I have the values, 1,2,3; and in Column 'B', I have the values, A, B, C. I want to list the values in column 'A' along with the value of say, B, in Column 'B'.

combobox.listfillrange = "Sheet1!A1:A3" < ---- how to include value B from column B in this list? Or, how to get value A from column B?

View 8 Replies View Related

Excel 2007 :: Retrieving Count Distribution Of Groups Of Cells?

Jan 27, 2013

I have a .csv open in Excel 2007 that looks like the following

Code:
1 566
1 566
1 566
2 341
2 341
3 566
3 566
3 566
3 566
...

There is a one-to-one relationship between Column A and B, but B is not unique (but can only take a small set of valid values). I wish to query how many id's (Column A) contain a particular property (Column B). If the example above ended before the "...", I would like to get as the output:

Code:
566 2
341 1

because the property 566 is owned by two id's (1 and 3) and the property 341 is only owned by the id 2.

View 3 Replies View Related

Excel 2010 :: Highlight Groups Of Identical Number Cells In Alternating Grey And White?

Jun 27, 2014

I am looking for a way to highlight groups of identical number cells in alternating grey and white. My goal is to make it clear when there is a repeating set of numbers. Below is an example of what a completed state of this would look like that I created manually. The real form I will be using this code on will have long number which is why I am looking for this added clarification. I am using excel 2010.

1

1

2

3

4

[code].....

View 4 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

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

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







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