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.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.