Sumif Criteria

[Nic]

I have a list of accounts and account numbers eg

A B C
1 11012 Personal exp 12000
2 11019 Business exp 10000
3 12089 Hotel exp 25000

i want to sum coulmn C if coulmn A falls between 11012 and 12089 the result
should show 10000

I have tried sumif(A1:A3,>11012<12089,C1:C3) value is shown as 0 which is
wrong.

I have also tried the sum(if( function but that will only work with one
coulmn.

Any ideas please help
Thanks

 

[Jacob Skaria]

Try

=SUMPRODUCT(--(A1:A100>11012),--(A1:A100<12089),C1:C100)

If this post helps click Yes

 

[Pete_UK]

Try this:

=SUMIF(A1:A3,"<12089",C1:C3) - SUMIF(A1:A3,"<=11012",C1:C3)

Hope this helps.

Pete

 

[Jacob Skaria]

As you mentioned you can also try with SUM(IF( as below. Please note that
this is an array formula. Within the cell in edit mode (F2) paste this
formula and press Ctrl+Shift+Enter to apply this formula. If successful in
'Formula Bar' you can notice the curly braces at both ends like "{=<formula>}"

=SUM(IF(A1:A100>11012,IF(A1:A100<12089,C1:C100)))

If this post helps click Yes

 

[Nic]

Thanks this works perfectly, just a question though what is the need for the
-- in the formula?

 

[Jacob Skaria]

-- is used to convert boolean values to numeric values . Check out the below
link
http://mcgimpsey.com/excel/formulae/doubleneg.html

Alternatively you can try

=SUMPRODUCT((A1:A101>11012)*(A1:A101<12089),C1:C101)

If this post helps click Yes

 

[David Biddulph]

Double unary minus:
a.. http://www.mcgimpsey.com/excel/formulae/doubleneg.html
b.. http://xldynamic.com/source/xld.SUMPRODUCT.html