| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMIF with criteria | Excel Worksheet Functions | |||
| SUMIF Criteria | Excel Worksheet Functions | |||
| SUMIF with four criteria | Excel Worksheet Functions | |||
| Sumif with 2 criteria | Excel Discussion (Misc queries) | |||
| nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
Linear Mode
