LinkBack URL
About LinkBacks
Is there a way to do a sumproduct of every item in a range with 1?
=SUMPRODUCT(A1:A5,{1,1,1,1,1}) does not work for whatever reason. Neither does =SUMPRODUCT(A1:A5,1). Any way to do this without a helper column? Thanks
Is there a way to do a sumproduct of every item in a range with 1?
=SUMPRODUCT(A1:A5,{1,1,1,1,1}) does not work for whatever reason. Neither does =SUMPRODUCT(A1:A5,1). Any way to do this without a helper column? Thanks
It does work, but the ranges you provide have to have compatible dimensions.
A1:A5 is a vertical array of five values but {1,1,1,1,1} is a horizontal array of five values.
Try this instead: =SUMPRODUCT(A1:A5,{1;1;1;1;1})
Last edited by eibi; 03-08-2016 at 11:50 PM.
I knew there had to be a reason that wouldn't work. Thanks. Now is there any way to make that second example work? The dimension of the range is unknown and I don't want to have to type a ton of 1's in...
What exactly are you trying to do here?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
When I need to return an array of 1's, I use something like this:
=row($A$1:A5)^0
I think you'll be able to produce the output you want like this:
=SUMPRODUCT($A$1:A5,ROW($A$1:A5)^0)
Entered as an array formula with Ctrl+Shift+Enter
--------------------------------------
Edited: CSE not needed -- see Khalidngo's correct observation, post #7, below.
Last edited by eibi; 03-09-2016 at 12:23 PM.
Thanks ikeene +1 that's perfect
Hi ikeene,Originally Posted by ikeene
CSE not necessary here
Blessing
Khalid
Funny -- I realized that too as I fell asleep last night...CSE not necessary here
Blessing
Good catch.
Blessings to you too sir.
Last edited by eibi; 03-09-2016 at 11:06 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks