Sumif function

[dolphinv4]

Hi,

I have 2 lists of data:

R1 R2
List 1
A $100 C1
B $150 C2
C $153 C3

List 2
Category 1 C4
A C5
Y C6
C C7
Category 2 C8
R C9
B C10
T C11

I have a cell whereby I want the cell to sum up the
amounts of each Category, ie, if the cell is to reflect
that for Category 1, it should show $253 but for Category
2, it should show $150. I tried to use the SUMIF
function, for example, for the cell for Cateogry
1, "=sumif(R1C1:R1C3,R1C5:R1C7,R2C1:R2C3)" but it doesn't
seem to work coz it seems like 2nd part of the function
cannot be a range. Is it correct? or can I actually get
around this?

Thanks,
val

 

[Jim Rech]

The criteria part of the SUMIF must be a constant or a formula that returns
a constant. I can't give you a solution since I do not understand your
example. In this example, which is in A1 format because I never use R1C1:

=SUMIF(A1:A10,C1,B1:B10)

where a code in the range A1:A10 matches the code in C1, the amounts in
B1:B10 are summed.

--
Jim Rech
Excel MVP
| Hi,
|
| I have 2 lists of data:
|
| R1 R2
| List 1
| A $100 C1
| B $150 C2
| C $153 C3
|
| List 2
| Category 1 C4
| A C5
| Y C6
| C C7
| Category 2 C8
| R C9
| B C10
| T C11
|
| I have a cell whereby I want the cell to sum up the
| amounts of each Category, ie, if the cell is to reflect
| that for Category 1, it should show $253 but for Category
| 2, it should show $150. I tried to use the SUMIF
| function, for example, for the cell for Cateogry
| 1, "=sumif(R1C1:R1C3,R1C5:R1C7,R2C1:R2C3)" but it doesn't
| seem to work coz it seems like 2nd part of the function
| cannot be a range. Is it correct? or can I actually get
| around this?
|
| Thanks,
| val

 

[Jerry W. Lewis]

=SUMPRODUCT((R1C1:R1C3=R1C5:R1C7)*R2C1:R2C3)

Jerry