Sumif question

[Guest]

Hi

I would like to perform a sumif based on 2 columns being the same.

I am able to run the basic SUMIF(A:A,A1.B:B) but I was wondering if it is
possible to do this based on 2 columns being the same. e.g I want to sum
column c if column a and b are the same.

Any help will be greatly appreciated.

 

[Guest]

=SUMPRODUCT(--(A2:A9=B2:B9),C2:C9)
Adjust ranges!

Regards,
Stefi


„David Bradford†ezt írta:

 

[JE McGimpsey]

One way:

=SUMPRODUCT(--(A1:A1000=B1:B1000),C1:C1000)

 

[Guest]

Sorry, I don't think I was clear. Column A and B won't be the same (as in
the same values)

Column A will specify one criteria (it is actually a code referring to a
fund) and column B will refer to another criteria (and asset within the
fund). I would like to be able to sum all like assets for each fund.

I toyed with if(sumif) but I don't know how this function works...

 

[Dave Peterson]

To sum the values in column C when column A matches one criteria and column B
matches another criteria:

=sumproduct(--(a1:a100="criteria1"),--(b1:b100="criteria2"),(c1:c100))

To count the number of items (rows) where column A matches one criteria and
column B matches another criteria:

=sumproduct(--(a1:a100="criteria1"),--(b1:b100="criteria2"))

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[JE McGimpsey]

In that case, I'd strongly recommend using a Pivot Table...

http://peltiertech.com/Excel/Pivots/pivotstart.htm