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),SUM(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" <[email protected]> wrote in message
news:[email protected]...
> 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),SUM(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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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),SUM(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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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?
>>
>>
>>