|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
|
Thread Tools | Display Modes |
|
#1
February 27th, 2007, 11:04 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
SumIf Function
I'm trying to use the SumIf Function (as I understand its use) and I keep
running into the same problems -- in many cases I have more than one criterion for addind a specific cell. Is there a way to modify SumIf to allow for multiple criteria? I try to do it in the insert function box and it gets spit back out at me. Any ideas? Jay 
|
|
#2
February 27th, 2007, 11:12 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SumIf Function
Jay
look at SUMPRODUCT Look at the Help and search the Archives for examples Regards Trevor "jayceejay" wrote in message ... I'm trying to use the SumIf Function (as I understand its use) and I keep running into the same problems -- in many cases I have more than one criterion for addind a specific cell. Is there a way to modify SumIf to allow for multiple criteria? I try to do it in the insert function box and it gets spit back out at me. Any ideas? Jay
|
|
#3
February 27th, 2007, 11:14 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SumIf Function
=sumproduct((a2:a22="Jay")*(b2:b22=1))
to sum c based on that criteria =sumproduct((a2:a22="Jay")*(b2:b22=1)*c2:c22) note that the ranges must be the same size and not complete columns -- Don Guillett SalesAid Software "jayceejay" wrote in message ... I'm trying to use the SumIf Function (as I understand its use) and I keep running into the same problems -- in many cases I have more than one criterion for addind a specific cell. Is there a way to modify SumIf to allow for multiple criteria? I try to do it in the insert function box and it gets spit back out at me. Any ideas? Jay
|
|
#4
February 27th, 2007, 11:28 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SumIf Function
More specifically, I'm saying Add the numbers in the range D2
 22 if A2:A22 ="Corporate" and If B2:B22 ="Zone4" I don't understand the * reference. "Don Guillett" wrote: =sumproduct((a2:a22="Jay")*(b2:b22=1)) to sum c based on that criteria =sumproduct((a2:a22="Jay")*(b2:b22=1)*c2:c22) note that the ranges must be the same size and not complete columns -- Don Guillett SalesAid Software "jayceejay" wrote in message ... I'm trying to use the SumIf Function (as I understand its use) and I keep running into the same problems -- in many cases I have more than one criterion for addind a specific cell. Is there a way to modify SumIf to allow for multiple criteria? I try to do it in the insert function box and it gets spit back out at me. Any ideas? Jay
|
|
#5
February 27th, 2007, 11:36 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SumIf Function
Hi
Then use =sumproduct((a2:a22="Corporate")*(b2:b22="Zone4")* d2:d22) a2:a22 will return True or False b2:b22 will return True or False The Multiplication will coerce the True's to 1's and the False's to 0's, hence 1 x 0 x 100 =0 1 x 1 x 150=150 0 x 1 x 120 =0 Sumproduct then just sums the results of these array multiplication to give your result -- Regards Roger Govier "jayceejay" wrote in message ... More specifically, I'm saying Add the numbers in the range D2 22 if A2:A22 ="Corporate" and If B2:B22 ="Zone4" I don't understand the * reference. "Don Guillett" wrote: =sumproduct((a2:a22="Jay")*(b2:b22=1)) to sum c based on that criteria =sumproduct((a2:a22="Jay")*(b2:b22=1)*c2:c22) note that the ranges must be the same size and not complete columns -- Don Guillett SalesAid Software "jayceejay" wrote in message ... I'm trying to use the SumIf Function (as I understand its use) and I keep running into the same problems -- in many cases I have more than one criterion for addind a specific cell. Is there a way to modify SumIf to allow for multiple criteria? I try to do it in the insert function box and it gets spit back out at me. Any ideas? Jay
|
|
#6
February 27th, 2007, 11:47 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SumIf Function
That's brilliant! I never looked at it that way (True =1, False =0). Pretty
obvious but I wasn't aware you could actually coerce the numeric value! Thank you Roger! "Roger Govier" wrote: Hi Then use =sumproduct((a2:a22="Corporate")*(b2:b22="Zone4")* d2:d22) a2:a22 will return True or False b2:b22 will return True or False The Multiplication will coerce the True's to 1's and the False's to 0's, hence 1 x 0 x 100 =0 1 x 1 x 150=150 0 x 1 x 120 =0 Sumproduct then just sums the results of these array multiplication to give your result -- Regards Roger Govier "jayceejay" wrote in message ... More specifically, I'm saying Add the numbers in the range D2 22 if A2:A22 ="Corporate" and If B2:B22 ="Zone4" I don't understand the * reference. "Don Guillett" wrote: =sumproduct((a2:a22="Jay")*(b2:b22=1)) to sum c based on that criteria =sumproduct((a2:a22="Jay")*(b2:b22=1)*c2:c22) note that the ranges must be the same size and not complete columns -- Don Guillett SalesAid Software "jayceejay" wrote in message ... I'm trying to use the SumIf Function (as I understand its use) and I keep running into the same problems -- in many cases I have more than one criterion for addind a specific cell. Is there a way to modify SumIf to allow for multiple criteria? I try to do it in the insert function box and it gets spit back out at me. Any ideas? Jay
|
|
#8
February 28th, 2007, 05:27 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SumIf Function
You know .......All it took was for someone to politely explain it to me.
I'm not used to thinking in terms of "adding" or "multiplying" TRUE and FALSE. Thanks for your concern, Don. "Don Guillett" wrote: Had you thought about what I sent you should have been able to modify it yourself. -- Don Guillett SalesAid Software "jayceejay" wrote in message ... That's brilliant! I never looked at it that way (True =1, False =0). Pretty obvious but I wasn't aware you could actually coerce the numeric value! Thank you Roger! "Roger Govier" wrote: Hi Then use =sumproduct((a2:a22="Corporate")*(b2:b22="Zone4")* d2:d22) a2:a22 will return True or False b2:b22 will return True or False The Multiplication will coerce the True's to 1's and the False's to 0's, hence 1 x 0 x 100 =0 1 x 1 x 150=150 0 x 1 x 120 =0 Sumproduct then just sums the results of these array multiplication to give your result -- Regards Roger Govier "jayceejay" wrote in message ... More specifically, I'm saying Add the numbers in the range D2 22 ifA2:A22 ="Corporate" and If B2:B22 ="Zone4" I don't understand the * reference. "Don Guillett" wrote: =sumproduct((a2:a22="Jay")*(b2:b22=1)) to sum c based on that criteria =sumproduct((a2:a22="Jay")*(b2:b22=1)*c2:c22) note that the ranges must be the same size and not complete columns -- Don Guillett SalesAid Software "jayceejay" wrote in message ... I'm trying to use the SumIf Function (as I understand its use) and I keep running into the same problems -- in many cases I have more than one criterion for addind a specific cell. Is there a way to modify SumIf to allow for multiple criteria? I try to do it in the insert function box and it gets spit back out at me. Any ideas? Jay
|
|
#9
February 28th, 2007, 12:08 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SumIf Function
And all I was saying is that you could have modified this to your need and
tried it. =sumproduct((a2:a22="Jay")*(b2:b22=1)) =sumproduct((a2:a22="corporate")*(b2:b22="zone4")* d2:d22) More specifically, I'm saying Add the numbers in the range D2 22 if A2:A22="Corporate" and If B2:B22 ="Zone4" I don't understand the * reference. ==================================== -- Don Guillett SalesAid Software "jayceejay" wrote in message ... You know .......All it took was for someone to politely explain it to me. I'm not used to thinking in terms of "adding" or "multiplying" TRUE and FALSE. Thanks for your concern, Don. "Don Guillett" wrote: Had you thought about what I sent you should have been able to modify it yourself. -- Don Guillett SalesAid Software "jayceejay" wrote in message ... That's brilliant! I never looked at it that way (True =1, False =0). Pretty obvious but I wasn't aware you could actually coerce the numeric value! Thank you Roger! "Roger Govier" wrote: Hi Then use =sumproduct((a2:a22="Corporate")*(b2:b22="Zone4")* d2:d22) a2:a22 will return True or False b2:b22 will return True or False The Multiplication will coerce the True's to 1's and the False's to 0's, hence 1 x 0 x 100 =0 1 x 1 x 150=150 0 x 1 x 120 =0 Sumproduct then just sums the results of these array multiplication to give your result -- Regards Roger Govier "jayceejay" wrote in message ... More specifically, I'm saying Add the numbers in the range D2 22 ifA2:A22 ="Corporate" and If B2:B22 ="Zone4" I don't understand the * reference. "Don Guillett" wrote: =sumproduct((a2:a22="Jay")*(b2:b22=1)) to sum c based on that criteria =sumproduct((a2:a22="Jay")*(b2:b22=1)*c2:c22) note that the ranges must be the same size and not complete columns -- Don Guillett SalesAid Software "jayceejay" wrote in message ... I'm trying to use the SumIf Function (as I understand its use) and I keep running into the same problems -- in many cases I have more than one criterion for addind a specific cell. Is there a way to modify SumIf to allow for multiple criteria? I try to do it in the insert function box and it gets spit back out at me. Any ideas? Jay
|
|
#10
March 1st, 2007, 03:40 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SumIf Function
XL help files contain *nothing* about all the possibilities that this
function can perform. Check out this link of Bob Philips for a truly enlightening explanation!g http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "jayceejay" wrote in message ... That's brilliant! I never looked at it that way (True =1, False =0). Pretty obvious but I wasn't aware you could actually coerce the numeric value! Thank you Roger! "Roger Govier" wrote: Hi Then use =sumproduct((a2:a22="Corporate")*(b2:b22="Zone4")* d2:d22) a2:a22 will return True or False b2:b22 will return True or False The Multiplication will coerce the True's to 1's and the False's to 0's, hence 1 x 0 x 100 =0 1 x 1 x 150=150 0 x 1 x 120 =0 Sumproduct then just sums the results of these array multiplication to give your result -- Regards Roger Govier "jayceejay" wrote in message ... More specifically, I'm saying Add the numbers in the range D2 22 ifA2:A22 ="Corporate" and If B2:B22 ="Zone4" I don't understand the * reference. "Don Guillett" wrote: =sumproduct((a2:a22="Jay")*(b2:b22=1)) to sum c based on that criteria =sumproduct((a2:a22="Jay")*(b2:b22=1)*c2:c22) note that the ranges must be the same size and not complete columns -- Don Guillett SalesAid Software "jayceejay" wrote in message ... I'm trying to use the SumIf Function (as I understand its use) and I keep running into the same problems -- in many cases I have more than one criterion for addind a specific cell. Is there a way to modify SumIf to allow for multiple criteria? I try to do it in the insert function box and it gets spit back out at me. Any ideas? Jay
|
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
