Pretty simple question here. How can I have it so the column (letter) changes in succession rather than the row (number)? Here is my formula :
=SUMPRODUCT(('Web Queries Reference'!A$2:A$600<>-1)*('Web Queries Reference'!A$2:A$600))-0.01
I would like it to be this once I drag the cell formula down to the next row :
=SUMPRODUCT(('Web Queries Reference'!B$2:B$600<>-1)*('Web Queries Reference'!B$2:B$600))-0.01
Any help would be appreciated!
try this
=SUMPRODUCT((INDEX(A$2:C$600,0,ROW(A1))<>-1)*(INDEX(A$2:C$600,0,ROW(A1))))
this will change the column number as you drag down
just keep the reference inside INDEX(A$2:C$600,0,ROW(A1)) to cover your full range i.e. if your data range is from A2:Z600 then keep full range of data that would be INDEX(A$2:Z$600,0,ROW(A1))<>-1 inside sumproduct
Last edited by hemesh; 04-06-2014 at 08:36 AM.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
Hmm...doesn't seem to be working. When I input that formula, I get really high numbers and furthermore it seems to still adjust only the rows when I drag rather than the columns (i.e., a1, a2, a3, etc...)
INDEX(A$2:Z$600,0,ROW(A1)) means column 1 of that index ie a2:a600
dragged down changes to
INDEX(A$2:Z$600,0,ROW(A2)) = column 2 of the index ie b2:b600 and so on
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
One way, maybe not the best:
Formula:
Please Login or Register to view this content.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
TMS, this formula seems to be working right! I am getting some reference errors while dragging, but that must be mistakes in the worksheet. I will let you know if there's any other problems. Thanks to the others too. I get the concept of using INDEX a little better now, it was just a matter of integrating it into this rather complex formula, which was trying to find the first value of each column that did not equal -1.
I think there might be some errors because the columns go past 'Z' (i.e. AA, AB, AC, etc...). Referencing A thru Z on my other worksheet gives me the values but anything past that is giving me the dreaded #REF! errors.
Sadly, that formula won't work for anything but single letter column references.
the indirect will have that limitation as using =CHAR(ROWS($A$1:A65)) which is A will only work up to =CHAR(ROWS($A$1:A90)) which is Z
once it hits =CHAR(ROWS($A$1:A91)) which is [ it will fail
thats why the index is more flexible
=SUMPRODUCT((INDEX('Web Queries Reference'!A$2:AZ$600,0,ROW(A1))<>-1)*(INDEX('Web Queries Reference'!A$2:AZ$600,0,ROW(A1))))-0.01 would work but why not
just sumif?
=SUMIF(INDEX('Web Queries Reference'!A$2:AZ$600,0,ROW(A1)),"<>-1")-0.01
Thanks for the reply. This is more complex than I am used to, so it's good to learn new things. About your last two suggestions :
The second one just returns -.01 but the first one *appears* to be working. I'll have to look it over in more detail. Just to be clear, the reference worksheet has around 700 or so columns...
so you want the first value that <>-1 thats a different thing
=INDEX(INDEX('Web Queries Reference'!$A$2:$AZ$600,0,ROWS($A$1:A1)),MATCH(TRUE,INDEX(INDEX('Web Queries Reference'!$A$2:$AZ$600,0,ROWS($A$1:A1))<>-1,0),0))-0.01
Okay, so it's looking like the formula you gave me is fool proof. Unfortunately my other formula is not.
On my reference sheet (Web Queries Reference), I have a formula such as this (in A2) :
=IFERROR(IF(FIND("New (",'Web Queries 2'!A2) = 0<>0,RIGHT('Web Queries 2'!A2,LEN('Web Queries 2'!A2)-FIND("$",'Web Queries 2'!A2)),0),-1)
My issue is somewhat similar to what I was trying to do already. In this case, I am just looking to drag the formula horizontally and have the letters increase by 5 each time. For example, I would like to get this once I drag it to the right (cell B2) :
=IFERROR(IF(FIND("New (",'Web Queries 2'!F2) = 0<>0,RIGHT('Web Queries 2'!F2,LEN('Web Queries 2'!F2)-FIND("$",'Web Queries 2'!F2)),0),-1)
So the increments would be A, F, K, and so on...
Last edited by supdawg1985; 04-06-2014 at 08:38 PM.
this bit
=FIND("New (",'Web Queries 2'!A2) = 0<>0 does not make sense as if it cannot find it it will not return anything other than a #value error
find will never = 0
to increment across columns
then where it says 'Web Queries 2'!A2 replace with
INDEX('Web Queries 2'!2:2,COLUMNS($A$1:A1)*5-4)
I think it worked. My computer can't process all this info very well though so it's taking awhile to update. I'll keep you posted. Thanks a ton for the help!
So close to having this spreadsheet fully functional. Apparently when dragging down, the reference stops at column AZ. Cells BA and on give me errors.
This is in regards to this formula, which works...for the most part.
=SUMPRODUCT((INDEX('Web Queries Reference'!A$2:AZ$600,0,ROW(A1))<>-1)*(INDEX('Web Queries Reference'!A$2:AZ$600,0,ROW(A1))))-0.01
Nevermind, just had to change the AZ to ZA in the formula...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks