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

  • Thread starter Thread starter mrmaw1
  • Start date Start date
M

mrmaw1

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
 
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"}))
 
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
 
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:C2>Data>Filter>Autofilter
Use dropdown on cell A2>Custom>Date>is greater than or equal to>05/02/09
AND
Date>is less than or equal to>09/02/09
 
Back
Top