• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Summing different number of cells

G

GN0001

Member
I have a worksheet with 1000 rows. Every several rows, after sorting I insert a row and in this row, I have to insert a sum function. Sometimes I have to sum 5 cells and sometimes I have to sum 20 cell and so on. Is there anyway that I can copy and paste the sum function into total lines( but these totals are not equal, they are total of unequal set of cells(say 5 cells or 15 cells, etc)? I mean the sum function is not summing all the time only 5 cells. It cells different number of cells. I appreciate your response.

Regards,

Guity
 
What determines when you add a SUM? If it is a value in another column, just use the SUBTOTAL function in Excel.
 
For example: I have a series of rows:

I have some products in one column that they belong to one group and in the next column, quantity for each product is entered, I want to total the quantities of each group. But the counts in each group is not equal. Some have 3 products and some have 25 products. This runs over 1000 rows. I sum the quantity for a group with 3 products, then the sum can't be copied and pasted for a group of 25 items. Since the first group has got 3 items only and second group has 25 item. Is there any way that we copy and past each sum function for each group? The groups are separated by one blank row for entering the total of quantity. If it is still not clear let me know.

Regards,

Guity
 
Guity

I assume your columns have headings, if not add some

Remove all the blank lines from your data

Select all your data area include headings

Goto the Data Ribbon, Outline Tab and select Subtotal

at each change in: select the field which defines the change of groups

Use Function Sum, or Count, as applicable

Add Subtotal to: Select the Column or Header which defines your quantity you want to sum/count

Replace subtotals Tick

and Ok


You can repeat the process once you understand what it is doing

It will add a Sub Total to a numeric column whenever the other column changes value

So your data must be sorted appropriately
 
Look at my list. Please see where I have inserted the total. I have these columns:


UD 24/7 E/L ZERO          A 17.00 50,626 53,118

UD 24/7 E/L BOURBON       A 17.00 13,694 15,321

UD 24/7 E/L RCKSTR PURSHM A 17.00 10,415 10,197

UD 24/7 E/P OIL SLICK     A 17.00 6,763 6,986

UD 24/7 E/L STASH GRN/GLD A 17.00 4,979 5,080

UD E/L 24/7 UNDERGROUND   A 17.00 4,409 4,762

UD 24/7 E/P BINGE         A 17.00 4,527 4,729

UD 24/7 E/L LUST          A 17.00 4,144 4,265

UD 24/7 E/P MILDEW        A 17.00 3,397 3,886

UD 24/7 E/L DEVIANT       A 17.00 3,662 3,609

UD E/L 24/7 RANSOM        A 17.00 3,621 3,512

UD 24/7 E/L YEYO WHTSHMMR A 17.00 3,110 3,353

UD 24/7 E/L ELECTRIC      A 17.00 3,171 3,146

UD 24/7 E/P CRASH         A 17.00 2,685 3,029

UD 24/7 E/L GUN METAL     A 17.00 2,971 2,798

UD 24/7 E/L 1999          A 17.00 3,033 2,787

UD 24/7 E/L COVET         A 17.00 2,331 2,310

UD 24/7 E/L LUCKY         A 17.00 1,948 2,190

UD E/L 24/7 FLIPSIDE      A 17.00 2,092 1,911

UD 24/7 EP ELDORADO       A 17.00 1,753 1,761

UD 24/7 E/L DIME          A 17.00 1,602 1,759

UD 24/7 E/L BAKED         A 17.00 1,972 1,720

UD E/L 24/7 GRAFFITI      A 17.00 1,653 1,562

Total Eye Pencils 143,790

UD PRIMER POTION NEW      A 18.00 85,094 88,355

UD SIN PRIMER POTION      A 18.00 25,954 26,844

UD PRIMER POTION EDEN     A 18.00 10,623 12,078

UD LIP PRIMER POTION      A 20.00 2,994 3,182

UD LASH PRIMER POTION     A 20.00 1,741 2,487

UD PORE PERF PRIMER POTN  A 30.00 3,628 2,197

UD BRITE PRIMER POTION    A 30.00 1,917 1,725

UD E/S TRNSFRMNG POTION   A 18.00 584 606

UD XL SIN PRIMER POTION   D 23.00 (12) 5

UD PRIMER POTION XL       D 22.00 0 0

UD PRIMER POTION OLD      D 17.00 (2) (51)

Total Potion 137,428

I have total Eye pencil and total Potion: The total Eye Pencil is 143,790 and total potion is 137,428. I can't copy the formula for Eye pencil to cell which has 137428, can I? Because Each time I sum different batch of cells. The batches are not equal.

I tried subtotal, it seems to me that this formula is not working for this purpose. I am just wondering if heading is coming to work in subTotal formula,

I need to think and try what you'r suggesting. However I am showing the scenario to you

Thank you so very much.

Guity
 
Guity


It is not clear from the above why/what you are summing to get 143,970

I can see that it is all the values in the right hand column above the total, but there is nothing in any of the other columns to tell me why they are all grouped the same

eg: the numbers that add to 143,970 all have a A and a 17 in 2 columns, but there is an A below that and a 17 at the bottom as well


Does your data have 8 columns ?


Can you copy that page out and post it somewhere for us to see

Please put column labels on to of each column


Can you try and explain what logic links all the numbers in your subtotals because it isn't clear from the data which are Pencils and which are Potions
 
Guity,


You might need a key.


So, where you have:


UD 24/7 E/L 1999 A 17.00 3,033 2,787

UD 24/7 E/L COVET A 17.00 2,331 2,310

UD 24/7 E/L LUCKY A 17.00 1,948 2,190

UD E/L 24/7 FLIPSIDE A 17.00 2,092 1,911

UD 24/7 EP ELDORADO A 17.00 1,753 1,761

UD 24/7 E/L DIME A 17.00 1,602 1,759

UD 24/7 E/L BAKED A 17.00 1,972 1,720

UD E/L 24/7 GRAFFITI A 17.00 1,653 1,562

Total Eye Pencils 143,790

UD E/S TRNSFRMNG POTION A 18.00 584 606

UD XL SIN PRIMER POTION D 23.00 (12) 5

UD PRIMER POTION XL D 22.00 0 0

UD PRIMER POTION OLD D 17.00 (2) (51)

Total Potion 137,428


You may need to add a column say....before UD so the data looks like this:

UD Eye Pencils E/L 24/7 GRAFFITI A 17.00 1,653 1,562

UD Potions PRIMER POTION OLD D 17.00 (2) (51)


Then you can use =sumif(B:B,"eye potion",E:E) to return 137428.


Or put them in a pivot and group the products manually...
 
Hui,

Where can I copy it? Since If I copy in this text box, it comes very confusing and also there are information from a company, If I expose it to public, I might be in trouble. However, I can send it to you individually. Please advise me. You are awesome....

Guity
 
You must log in or register to reply here.
Back
Top