how to find the sum of certain values in a column.

I want to be able to find the sum of all values in a column that have a
particular value in the previous column, sush as follows.

A B
1 TYPE VALUE
2 a 10
3 b 10
4 a 10
5 c 10
6 b 10
7 a 10
8 c 10
9 b 10
10 c 10
11 a 10
12 TOTAL 100

I'm able to total the column fine but what I want is the following:

A) I want is a formula that will total only those VALUES that are TYPE "a"
so that I can multiply the sum by a factor of Y. In this case the answer
would be 40(Y).

B) And I want a different formula that will total those VALUES that are
either TYPE "b" or "c" so that I can multiply the sum by a factor of Z. In
this case the answer would be 60(Z).

Any help would be greatly appreciated.
Thank you.

Look in the help index for
SUMIF
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"mrmaw1" wrote in message
...
I want to be able to find the sum of all values in a column that have a
particular value in the previous column, sush as follows.

A B
1 TYPE VALUE
2 a 10
3 b 10
4 a 10
5 c 10
6 b 10
7 a 10
8 c 10
9 b 10
10 c 10
11 a 10
12 TOTAL 100

I'm able to total the column fine but what I want is the following:

A) I want is a formula that will total only those VALUES that are TYPE "a"
so that I can multiply the sum by a factor of Y. In this case the answer
would be 40(Y).

B) And I want a different formula that will total those VALUES that are
either TYPE "b" or "c" so that I can multiply the sum by a factor of Z. In
this case the answer would be 60(Z).

Any help would be greatly appreciated.
Thank you.

Try these:

A: =SUMIF(A2:A11."A",B2:B11)

B: =SUM(SUMIF(A2:A11,{"B","C"},B2:B11))

--
Biff
Microsoft Excel MVP


"mrmaw1" wrote in message
...
I want to be able to find the sum of all values in a column that have a
particular value in the previous column, sush as follows.

A B
1 TYPE VALUE
2 a 10
3 b 10
4 a 10
5 c 10
6 b 10
7 a 10
8 c 10
9 b 10
10 c 10
11 a 10
12 TOTAL 100

I'm able to total the column fine but what I want is the following:

A) I want is a formula that will total only those VALUES that are TYPE "a"
so that I can multiply the sum by a factor of Y. In this case the answer
would be 40(Y).

B) And I want a different formula that will total those VALUES that are
either TYPE "b" or "c" so that I can multiply the sum by a factor of Z. In
this case the answer would be 60(Z).

Any help would be greatly appreciated.
Thank you.

A) =SUMIF(A2:A12,"A",B2:B12)

B) =SUMPRODUCT(((A3:A12="B")+(A3:A12="C")),B3:B12)

--
Hope this is helpful

Appreciate that you provide your feedback by clicking the Yes button below
if this post have helped you.


Thank You

cheers, francis










"mrmaw1" wrote:

I want to be able to find the sum of all values in a column that have a
particular value in the previous column, sush as follows.

A B
1 TYPE VALUE
2 a 10
3 b 10
4 a 10
5 c 10
6 b 10
7 a 10
8 c 10
9 b 10
10 c 10
11 a 10
12 TOTAL 100

I'm able to total the column fine but what I want is the following:

A) I want is a formula that will total only those VALUES that are TYPE "a"
so that I can multiply the sum by a factor of Y. In this case the answer
would be 40(Y).

B) And I want a different formula that will total those VALUES that are
either TYPE "b" or "c" so that I can multiply the sum by a factor of Z. In
this case the answer would be 60(Z).

Any help would be greatly appreciated.
Thank you.

Hi,

You can use any of the following minor additons to those already supplied:

=SUMPRODUCT((A2:A11="B")+(A2:A11="C"),B2:B11)

=SUMPRODUCT((A2:A11={"B","C"})*B2:B11)

or in 2007:
=SUM(SUMIFS(B2:B11,A2:A11,{"B","C"}))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"mrmaw1" wrote:

I want to be able to find the sum of all values in a column that have a
particular value in the previous column, sush as follows.

A B
1 TYPE VALUE
2 a 10
3 b 10
4 a 10
5 c 10
6 b 10
7 a 10
8 c 10
9 b 10
10 c 10
11 a 10
12 TOTAL 100

I'm able to total the column fine but what I want is the following:

A) I want is a formula that will total only those VALUES that are TYPE "a"
so that I can multiply the sum by a factor of Y. In this case the answer
would be 40(Y).

B) And I want a different formula that will total those VALUES that are
either TYPE "b" or "c" so that I can multiply the sum by a factor of Z. In
this case the answer would be 60(Z).

Any help would be greatly appreciated.
Thank you.

Dear all,

I would like to

Q1. sum up the date criteria between 05/02 and 09/02 and the answer shows
1,050. Please advise.

Column A Column B Column C
05/02 1,000 ABC Ltd
07/02 40 XYZ Ltd
09/02 10 JJJ Ltd
11/02 100 DDD Ltd

Q2. Referring to the above, I would also like to show the Column C
information based on the abovementioned date range. The ideal result is in
vertical format:
Please advise the related formula or any alternative.

ABC Ltd
XYZ Ltd
JJJ Ltd


Look forward to your help soonest possible



"Shane Devenshire" wrote:

Hi,

You can use any of the following minor additons to those already supplied:

=SUMPRODUCT((A2:A11="B")+(A2:A11="C"),B2:B11)

=SUMPRODUCT((A2:A11={"B","C"})*B2:B11)

or in 2007:
=SUM(SUMIFS(B2:B11,A2:A11,{"B","C"}))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"mrmaw1" wrote:

I want to be able to find the sum of all values in a column that have a
particular value in the previous column, sush as follows.

A B
1 TYPE VALUE
2 a 10
3 b 10
4 a 10
5 c 10
6 b 10
7 a 10
8 c 10
9 b 10
10 c 10
11 a 10
12 TOTAL 100

I'm able to total the column fine but what I want is the following:

A) I want is a formula that will total only those VALUES that are TYPE "a"
so that I can multiply the sum by a factor of Y. In this case the answer
would be 40(Y).

B) And I want a different formula that will total those VALUES that are
either TYPE "b" or "c" so that I can multiply the sum by a factor of Z. In
this case the answer would be 60(Z).

Any help would be greatly appreciated.
Thank you.

Hi

To answer both questions in one.
Insert 2 rows above your data.
On row 2 enter Date, Value and Name into cells A2:C2
In cell B1 enter
=SUBTOTAL(9,B3:B100)
Select cells A2:C2DataFilterAutofilter
Use dropdown on cell A2CustomDateis greater than or equal to05/02/09
AND
Dateis less than or equal to09/02/09

--
Regards
Roger Govier

"URGENT" wrote in message
...
Dear all,

I would like to

Q1. sum up the date criteria between 05/02 and 09/02 and the answer shows
1,050. Please advise.

Column A Column B Column C
05/02 1,000 ABC Ltd
07/02 40 XYZ Ltd
09/02 10 JJJ Ltd
11/02 100 DDD Ltd

Q2. Referring to the above, I would also like to show the Column C
information based on the abovementioned date range. The ideal result is
in
vertical format:
Please advise the related formula or any alternative.

ABC Ltd
XYZ Ltd
JJJ Ltd


Look forward to your help soonest possible



"Shane Devenshire" wrote:

Hi,

You can use any of the following minor additons to those already
supplied:

=SUMPRODUCT((A2:A11="B")+(A2:A11="C"),B2:B11)

=SUMPRODUCT((A2:A11={"B","C"})*B2:B11)

or in 2007:
=SUM(SUMIFS(B2:B11,A2:A11,{"B","C"}))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"mrmaw1" wrote:

I want to be able to find the sum of all values in a column that have a
particular value in the previous column, sush as follows.

A B
1 TYPE VALUE
2 a 10
3 b 10
4 a 10
5 c 10
6 b 10
7 a 10
8 c 10
9 b 10
10 c 10
11 a 10
12 TOTAL 100

I'm able to total the column fine but what I want is the following:

A) I want is a formula that will total only those VALUES that are TYPE
"a"
so that I can multiply the sum by a factor of Y. In this case the
answer
would be 40(Y).

B) And I want a different formula that will total those VALUES that are
either TYPE "b" or "c" so that I can multiply the sum by a factor of Z.
In
this case the answer would be 60(Z).

Any help would be greatly appreciated.
Thank you.