Multiple Criteria for SumIf function

V

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!
 
D

Don Guillett

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

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

Ron Coderre

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
 
S

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
 
V

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!
Click to expand...
 
Top