| Home |
| Search |
| Today's Posts |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct #value!
Thanks. Anything to make it easier is good.
"JE McGimpsey" wrote: Actually, if you use the "correct" syntax of SUMPRODUCT, you can use labels just fine, as SUMPRODUCT is set up to disregard anything than's non-numeric. Instead of multiplying the ranges in the argument (so that the arrays are multiplied prior to being handed to SUMPRODUCT), enter them as separate arguments. For instance, instead of =SUMPRODUCT(a * b * c) use =SUMPRODUCT(a, b, c) for arrays of the form (A1:A1000=5), which return booleans, use double negation to coerce the boolean into a numeric value: =SUMPRODUCT(--(a)=0), --(a<=100),c) See http://www.mcgimpsey.com/excel/doubleneg.html for more explanation. As an added benefit, passing the arrays separately is at least slightly faster than multiplying them first. In article , wal50 wrote: The only thing that wasn't a number was the column lable in row 1. When I made the range C2:C8497, it worked. Thanks for the hint. I guess I should leave out the label row in the future. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need help with sumproduct & dynamic ranges | Excel Worksheet Functions | |||
| Need help with sumproduct and dynamic ranges | Excel Worksheet Functions | |||
| Can I reference =, <, or > sign in SUMPRODUCT | Excel Discussion (Misc queries) | |||
| Sumproduct function not working | Excel Worksheet Functions | |||
| adding two sumproduct formulas together | Excel Worksheet Functions | |||
Threaded Mode
