Multiple Criteria for SumIf function

[vgarrow]

I have the following data:

Account External ref 2 Value
5950.799.MR05 976 158.64
5950.799.MR10 976 84.96
5950.799.MR05 978 108.12
5950.799.MR15 506 158.64
5950.799.MR05 506 65.44
5950.799.MR05 976 83.28
5950.799.MR05 506 103.12
5950.799.MR10 976 158.64
5950.799.MR10 976 158.64

I would like to use a SumIf function using two criteria. If the
account number in Column A = 5950.799.MR05 and the Ext Ref 2 (Column B)
= 506 then sum the value in Column C

I tried using the And statement in conjuction with the SumIf but it
didn't work.

Any help would be greatly appreciated!

Thanks!

 

[Don Guillett]

use sumproduct
to count
=sumproduct((a2:a22=1)*(b2:b22=2))

to sum
=sumproduct((a2:a22=1)*(b2:b22=2)*c2:c22)

 

[Guest]

Try this:

Using your sample data in A1:C10, with Row_1 holding the col titles

D1: =SUMPRODUCT((A2:A10="5950.799.MR05")*(B2:B10=506)*(C2:C10))

Adjust range references to suit your situation.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[SteveG]

Try using SUMPRODUCT instead.

=SUMPRODUCT((A1:A9=K1)*(B1:B9=L1)*C1:C9)

Where K1 = the account # and L1 = Ext Ref 2 (506).

HTH

Stev

 

[vgarrow]

I have the following data:

Account External ref 2 Value
5950.799.MR05 976 158.64
5950.799.MR10 976 84.96
5950.799.MR05 978 108.12
5950.799.MR15 506 158.64
5950.799.MR05 506 65.44
5950.799.MR05 976 83.28
5950.799.MR05 506 103.12
5950.799.MR10 976 158.64
5950.799.MR10 976 158.64

I would like to use a SumIf function using two criteria. If the
account number in Column A = 5950.799.MR05 and the Ext Ref 2 (Column B)
= 506 then sum the value in Column C

I tried using the And statement in conjuction with the SumIf but it
didn't work.

Any help would be greatly appreciated!

Thanks!

 

[vgarrow]

Thank you very much! That was driving me crazy!
Have a good day!
V

 

[Don Guillett]

We ALL thank you for the feedback