Sumif Criteria
 

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

Sumif Criteria
 

Try

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

If this post helps click Yes
---------------
Jacob Skaria


"Nic" wrote:

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

Sumif Criteria
 

Try this:

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

Hope this helps.

Pete

On Aug 4, 9:53*am, Nic wrote:
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

Sumif Criteria
 

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:A10011012,IF(A1:A100<12089,C1:C100)))

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Try

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

If this post helps click Yes
---------------
Jacob Skaria


"Nic" wrote:

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

Sumif Criteria
 

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

"Jacob Skaria" wrote:

Try

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

If this post helps click Yes
---------------
Jacob Skaria


"Nic" wrote:

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

Sumif Criteria
 

-- 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:A10111012)*(A1:A101<12089),C1:C10 1)

If this post helps click Yes
---------------
Jacob Skaria


"Nic" wrote:

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

"Jacob Skaria" wrote:

Try

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

If this post helps click Yes
---------------
Jacob Skaria


"Nic" wrote:

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

Sumif Criteria
 

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

"Nic" wrote in message
...
Thanks this works perfectly, just a question though what is the need for
the
-- in the formula?

"Jacob Skaria" wrote:

Try

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

If this post helps click Yes
---------------
Jacob Skaria


"Nic" wrote:

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