|
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 |
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 |
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 |
SumIf Function
More specifically, I'm saying Add the numbers in the range D2:D22 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 |
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:D22 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 |
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:D22 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 |
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:D22 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 |
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:D22 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:D22 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 |
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:D22 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 |
SumIf Function
pleasae learing me sumproduct formula example
"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 |
| All times are GMT +1. The time now is 09:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com