Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Creating Formulas
Please forgive me if this posts twice- this is my first time to use this
message board, and I don't know what I am doing. Assuming there are 500 rows of information consisting of 2 or more columns. Is it possible to create a formula that would total the sum of a group of cells in Column B if all the cells in column A are identical? |
#2
|
|||
|
|||
Hi
need a bit more info, do you mean ..........A..............B 1.....Item 1.........50 2....Item 1..........20 3....Item 1.........300 etc for 500 rows meaning that all of column A is identical, so you now want to add B or does your data also include .........A................B 4.....Item 2........20 5.....Item 2........10 and you want a total of column B for Item 1 and a total of column B for item 2 if it is the first option then use a formula like (assuming headings in row 1) =IF(COUNTIF($A$2:$A$500,A2)=COUNTA($A$2:$A$500),SU M(B2:B500),"mismatch") if it is the second option you have a number of different ways of approaching it 1) if you can sort by column A (choose data / sort - column A), sort first and then you can use the SUBTOTAL feature which subtotals on a particular value (data /subtotals/ at each change in <choose whatever's in column A, SUM, <choose the heading for column B) 2) you can use the SUMIF function (data does not need to be sorted for this one) =SUMIF(A2:A500,"Item 1", B2:B500) =SUMIF(A2:A500,"Item 2", B2:B500) 3) you can use pivot tables - check out www.contextures.com/tiptech.html for details on pivot tables Cheers JulieD PS please always try something new on a copy of the workbook first. "samsmimi" wrote in message ... Please forgive me if this posts twice- this is my first time to use this message board, and I don't know what I am doing. Assuming there are 500 rows of information consisting of 2 or more columns. Is it possible to create a formula that would total the sum of a group of cells in Column B if all the cells in column A are identical? |
#3
|
|||
|
|||
Your second example better describes what I am needing. I will work with the
formulas you suggested- I think that will solve my problem! Thanks so much. Blessings! "JulieD" wrote: Hi need a bit more info, do you mean ..........A..............B 1.....Item 1.........50 2....Item 1..........20 3....Item 1.........300 etc for 500 rows meaning that all of column A is identical, so you now want to add B or does your data also include .........A................B 4.....Item 2........20 5.....Item 2........10 and you want a total of column B for Item 1 and a total of column B for item 2 if it is the first option then use a formula like (assuming headings in row 1) =IF(COUNTIF($A$2:$A$500,A2)=COUNTA($A$2:$A$500),SU M(B2:B500),"mismatch") if it is the second option you have a number of different ways of approaching it 1) if you can sort by column A (choose data / sort - column A), sort first and then you can use the SUBTOTAL feature which subtotals on a particular value (data /subtotals/ at each change in <choose whatever's in column A, SUM, <choose the heading for column B) 2) you can use the SUMIF function (data does not need to be sorted for this one) =SUMIF(A2:A500,"Item 1", B2:B500) =SUMIF(A2:A500,"Item 2", B2:B500) 3) you can use pivot tables - check out www.contextures.com/tiptech.html for details on pivot tables Cheers JulieD PS please always try something new on a copy of the workbook first. "samsmimi" wrote in message ... Please forgive me if this posts twice- this is my first time to use this message board, and I don't know what I am doing. Assuming there are 500 rows of information consisting of 2 or more columns. Is it possible to create a formula that would total the sum of a group of cells in Column B if all the cells in column A are identical? |
#4
|
|||
|
|||
you're welcome and thanks for the feedback
"samsmimi" wrote in message ... Your second example better describes what I am needing. I will work with the formulas you suggested- I think that will solve my problem! Thanks so much. Blessings! "JulieD" wrote: Hi need a bit more info, do you mean ..........A..............B 1.....Item 1.........50 2....Item 1..........20 3....Item 1.........300 etc for 500 rows meaning that all of column A is identical, so you now want to add B or does your data also include .........A................B 4.....Item 2........20 5.....Item 2........10 and you want a total of column B for Item 1 and a total of column B for item 2 if it is the first option then use a formula like (assuming headings in row 1) =IF(COUNTIF($A$2:$A$500,A2)=COUNTA($A$2:$A$500),SU M(B2:B500),"mismatch") if it is the second option you have a number of different ways of approaching it 1) if you can sort by column A (choose data / sort - column A), sort first and then you can use the SUBTOTAL feature which subtotals on a particular value (data /subtotals/ at each change in <choose whatever's in column A, SUM, <choose the heading for column B) 2) you can use the SUMIF function (data does not need to be sorted for this one) =SUMIF(A2:A500,"Item 1", B2:B500) =SUMIF(A2:A500,"Item 2", B2:B500) 3) you can use pivot tables - check out www.contextures.com/tiptech.html for details on pivot tables Cheers JulieD PS please always try something new on a copy of the workbook first. "samsmimi" wrote in message ... Please forgive me if this posts twice- this is my first time to use this message board, and I don't know what I am doing. Assuming there are 500 rows of information consisting of 2 or more columns. Is it possible to create a formula that would total the sum of a group of cells in Column B if all the cells in column A are identical? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating Formulas for overtime | Excel Worksheet Functions | |||
Problem with named formula's | Excel Worksheet Functions | |||
How to make Excel run limited number of formulas on a given worksh | Excel Discussion (Misc queries) | |||
Way to make Excel only run certain formulas on a worksheet? | Excel Discussion (Misc queries) | |||
calculating formulas for all workbooks in a folder | Excel Worksheet Functions |