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?

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?

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?

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?