How do I create a function that adds up certain numbers in one column that
all have a certain value in another column. For example:
column A Column B
557 4
557 6
445 7
336 20

The values are offset by different numbers of rows as well. Ideally I'd want
to know the sum of all of Column B whose Column A value is the same. There
are about a 1000 of unique values in Column A so I don't want to have to go
and do this by hand.

Thanks.

Well, the easiest way would probably be a Pivot Table:
http://peltiertech.com/Excel/Pivots/pivottables.htm
http://www.babeled.com/2008/07/18/ex...-manipulation/


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"JD" wrote:

How do I create a function that adds up certain numbers in one column that
all have a certain value in another column. For example:
column A Column B
557 4
557 6
445 7
336 20

The values are offset by different numbers of rows as well. Ideally I'd want
to know the sum of all of Column B whose Column A value is the same. There
are about a 1000 of unique values in Column A so I don't want to have to go
and do this by hand.

Thanks.

What about Subtotals:
http://office.microsoft.com/en-us/ex...097981033.aspx
http://pubs.logicalexpressions.com/P...cle.asp?ID=358



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"JD" wrote:

How do I create a function that adds up certain numbers in one column that
all have a certain value in another column. For example:
column A Column B
557 4
557 6
445 7
336 20

The values are offset by different numbers of rows as well. Ideally I'd want
to know the sum of all of Column B whose Column A value is the same. There
are about a 1000 of unique values in Column A so I don't want to have to go
and do this by hand.

Thanks.

Thanks for the suggestion. Using the subtotal method (or any other) I also
have a column that already has the predetermined total of the subareas
(column b in my example) that should presumable be the same as the calculated
subtotal. Is there anyway to determine with true/false if the subtotals of
each unique value in column a as we just calculated equal the predetermined
value that was given?

column A Column B Column C
557 4 10
557 6 10
445 7 7
336 20 20

"ryguy7272" wrote:

What about Subtotals:
http://office.microsoft.com/en-us/ex...097981033.aspx
http://pubs.logicalexpressions.com/P...cle.asp?ID=358



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"JD" wrote:

How do I create a function that adds up certain numbers in one column that
all have a certain value in another column. For example:
column A Column B
557 4
557 6
445 7
336 20

The values are offset by different numbers of rows as well. Ideally I'd want
to know the sum of all of Column B whose Column A value is the same. There
are about a 1000 of unique values in Column A so I don't want to have to go
and do this by hand.

Thanks.

Also, I care only if the calculated subtotal is greater than or less than 5
than the predetermined number in column C

"JD" wrote:

Thanks for the suggestion. Using the subtotal method (or any other) I also
have a column that already has the predetermined total of the subareas
(column b in my example) that should presumable be the same as the calculated
subtotal. Is there anyway to determine with true/false if the subtotals of
each unique value in column a as we just calculated equal the predetermined
value that was given?

column A Column B Column C
557 4 10
557 6 10
445 7 7
336 20 20

"ryguy7272" wrote:

What about Subtotals:
http://office.microsoft.com/en-us/ex...097981033.aspx
http://pubs.logicalexpressions.com/P...cle.asp?ID=358



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"JD" wrote:

How do I create a function that adds up certain numbers in one column that
all have a certain value in another column. For example:
column A Column B
557 4
557 6
445 7
336 20

The values are offset by different numbers of rows as well. Ideally I'd want
to know the sum of all of Column B whose Column A value is the same. There
are about a 1000 of unique values in Column A so I don't want to have to go
and do this by hand.

Thanks.

Maybe this:
=SUMPRODUCT(--(A1:A5=C1),--(B1:B5))

557 is in Cell C1

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"JD" wrote:

Also, I care only if the calculated subtotal is greater than or less than 5
than the predetermined number in column C

"JD" wrote:

Thanks for the suggestion. Using the subtotal method (or any other) I also
have a column that already has the predetermined total of the subareas
(column b in my example) that should presumable be the same as the calculated
subtotal. Is there anyway to determine with true/false if the subtotals of
each unique value in column a as we just calculated equal the predetermined
value that was given?

column A Column B Column C
557 4 10
557 6 10
445 7 7
336 20 20

"ryguy7272" wrote:

What about Subtotals:
http://office.microsoft.com/en-us/ex...097981033.aspx
http://pubs.logicalexpressions.com/P...cle.asp?ID=358



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"JD" wrote:

How do I create a function that adds up certain numbers in one column that
all have a certain value in another column. For example:
column A Column B
557 4
557 6
445 7
336 20

The values are offset by different numbers of rows as well. Ideally I'd want
to know the sum of all of Column B whose Column A value is the same. There
are about a 1000 of unique values in Column A so I don't want to have to go
and do this by hand.

Thanks.

I don't understand what you mean by the last posting.

In my example below how would you verify that D1=C1 or if there were
subtotals of column B for each group in column A how would you compare them
to the values in column D.

"APN" "Square feet" "Sum of B"
"Input"
column A Column B Column C
Column D
Row 1 557 4 10
11
Row 2 557 6 10
11
Row 3 445 7 7
7
Row 4 336 20 20
7

"ryguy7272" wrote:

Maybe this:
=SUMPRODUCT(--(A1:A5=C1),--(B1:B5))

557 is in Cell C1

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"JD" wrote:

Also, I care only if the calculated subtotal is greater than or less than 5
than the predetermined number in column C

"JD" wrote:

Thanks for the suggestion. Using the subtotal method (or any other) I also
have a column that already has the predetermined total of the subareas
(column b in my example) that should presumable be the same as the calculated
subtotal. Is there anyway to determine with true/false if the subtotals of
each unique value in column a as we just calculated equal the predetermined
value that was given?

column A Column B Column C
557 4 10
557 6 10
445 7 7
336 20 20

"ryguy7272" wrote:

What about Subtotals:
http://office.microsoft.com/en-us/ex...097981033.aspx
http://pubs.logicalexpressions.com/P...cle.asp?ID=358



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"JD" wrote:

How do I create a function that adds up certain numbers in one column that
all have a certain value in another column. For example:
column A Column B
557 4
557 6
445 7
336 20

The values are offset by different numbers of rows as well. Ideally I'd want
to know the sum of all of Column B whose Column A value is the same. There
are about a 1000 of unique values in Column A so I don't want to have to go
and do this by hand.

Thanks.

Hey JD! I think you lost me here. At first, I thought this was a very
simple thing, but now I can't tell what you want. Please repost; your
questions will reappear at the top of the list and you should get some
responses pretty quick. Just try to describe the question differently from
the way you did before. Towards the end, I couldn't tell what was going on,
and I suspect others couldn't either, and that's why no one else jumpped in
here.

Good luck!!

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"JD" wrote:

I don't understand what you mean by the last posting.

In my example below how would you verify that D1=C1 or if there were
subtotals of column B for each group in column A how would you compare them
to the values in column D.

"APN" "Square feet" "Sum of B"
"Input"
column A Column B Column C
Column D
Row 1 557 4 10
11
Row 2 557 6 10
11
Row 3 445 7 7
7
Row 4 336 20 20
7

"ryguy7272" wrote:

Maybe this:
=SUMPRODUCT(--(A1:A5=C1),--(B1:B5))

557 is in Cell C1

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"JD" wrote:

Also, I care only if the calculated subtotal is greater than or less than 5
than the predetermined number in column C

"JD" wrote:

Thanks for the suggestion. Using the subtotal method (or any other) I also
have a column that already has the predetermined total of the subareas
(column b in my example) that should presumable be the same as the calculated
subtotal. Is there anyway to determine with true/false if the subtotals of
each unique value in column a as we just calculated equal the predetermined
value that was given?

column A Column B Column C
557 4 10
557 6 10
445 7 7
336 20 20

"ryguy7272" wrote:

What about Subtotals:
http://office.microsoft.com/en-us/ex...097981033.aspx
http://pubs.logicalexpressions.com/P...cle.asp?ID=358



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"JD" wrote:

How do I create a function that adds up certain numbers in one column that
all have a certain value in another column. For example:
column A Column B
557 4
557 6
445 7
336 20

The values are offset by different numbers of rows as well. Ideally I'd want
to know the sum of all of Column B whose Column A value is the same. There
are about a 1000 of unique values in Column A so I don't want to have to go
and do this by hand.

Thanks.