|
i HAVE A PROBLEM
i HAVE A COLUMN OF EXPENCES THAT IS SUB TOTALLED EVERY 103 ROW USING =SUM(H113:H213),=SUM(H216:H316) etc,etc,tec These sub totals are picked up by =SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),10 3)=8)) All works perfectley until I put a Hyperlink cell in any H ROW AFTER 111. then a #VALUE! COME S UP IN THE =SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),10 3)=8)) BOX. ALLTHE SUB TOTALS ARE FINE AND READ PERFECTLY. My understanding was that =SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),10 3)=8)) only add from the row numbers that divide by 103 and give the remainder of 8 so why is it giving this #VALUE! sYMBOL when all the sub totals are reading correctly If i put a Hyperlink in rows up to 111 then this error does not occur Help Stew |
i HAVE A PROBLEM
Not sure my previous post got through..(with detailed explanation)
Use =SUMPRODUCT((H111:H25655),(MOD(ROW(H111:H25655),10 3)=8)) "stew" wrote: i HAVE A COLUMN OF EXPENCES THAT IS SUB TOTALLED EVERY 103 ROW USING =SUM(H113:H213),=SUM(H216:H316) etc,etc,tec These sub totals are picked up by =SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),10 3)=8)) All works perfectley until I put a Hyperlink cell in any H ROW AFTER 111. then a #VALUE! COME S UP IN THE =SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),10 3)=8)) BOX. ALLTHE SUB TOTALS ARE FINE AND READ PERFECTLY. My understanding was that =SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),10 3)=8)) only add from the row numbers that divide by 103 and give the remainder of 8 so why is it giving this #VALUE! sYMBOL when all the sub totals are reading correctly If i put a Hyperlink in rows up to 111 then this error does not occur Help Stew |
i HAVE A PROBLEM
Hi
Thanks for helping. I have substituted your suggested change and now it does not add the subtotals. Can you give me the explanation again ,as I did not get it and I may be able to work it out, however removing the * does not seem to work Stew "Sheeloo" wrote: Not sure my previous post got through..(with detailed explanation) Use =SUMPRODUCT((H111:H25655),(MOD(ROW(H111:H25655),10 3)=8)) "stew" wrote: i HAVE A COLUMN OF EXPENCES THAT IS SUB TOTALLED EVERY 103 ROW USING =SUM(H113:H213),=SUM(H216:H316) etc,etc,tec These sub totals are picked up by =SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),10 3)=8)) All works perfectley until I put a Hyperlink cell in any H ROW AFTER 111. then a #VALUE! COME S UP IN THE =SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),10 3)=8)) BOX. ALLTHE SUB TOTALS ARE FINE AND READ PERFECTLY. My understanding was that =SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),10 3)=8)) only add from the row numbers that divide by 103 and give the remainder of 8 so why is it giving this #VALUE! sYMBOL when all the sub totals are reading correctly If i put a Hyperlink in rows up to 111 then this error does not occur Help Stew |
i HAVE A PROBLEM
Sorry, I forgot to add --. Pl. use the following
=SUMPRODUCT((H111:H25655),--(MOD(ROW(H111:H25655),103)=8)) If you use * then both arrays are multiplied and then passed to SUMPRODUCT. Since there is a #Value! due to the hyperlink result is a #Value!. When you use ',' arrays are passed to SUMPRODUCT which ignores non-numeric values before multiplying and adding... Without '--' one gets and array of TRUE/FALSE which get converted to 1/0 with '--'. "stew" wrote: Hi Thanks for helping. I have substituted your suggested change and now it does not add the subtotals. Can you give me the explanation again ,as I did not get it and I may be able to work it out, however removing the * does not seem to work Stew "Sheeloo" wrote: Not sure my previous post got through..(with detailed explanation) Use =SUMPRODUCT((H111:H25655),(MOD(ROW(H111:H25655),10 3)=8)) "stew" wrote: i HAVE A COLUMN OF EXPENCES THAT IS SUB TOTALLED EVERY 103 ROW USING =SUM(H113:H213),=SUM(H216:H316) etc,etc,tec These sub totals are picked up by =SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),10 3)=8)) All works perfectley until I put a Hyperlink cell in any H ROW AFTER 111. then a #VALUE! COME S UP IN THE =SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),10 3)=8)) BOX. ALLTHE SUB TOTALS ARE FINE AND READ PERFECTLY. My understanding was that =SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),10 3)=8)) only add from the row numbers that divide by 103 and give the remainder of 8 so why is it giving this #VALUE! sYMBOL when all the sub totals are reading correctly If i put a Hyperlink in rows up to 111 then this error does not occur Help Stew |
i HAVE A PROBLEM
Dear Sandy
I would but this sheet already exists and you can see from its size it would take a lot of work to start changing now. I Do not understand why all the sub totals show fine when the hyperlinks are in but the =SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),10 3)=8)) is returning #VALUE!. THE FORMULA IS ONLY SUPPOSED TO PICK OUT THE SUB TOTAL BOXES WHICH ALL DISPLAY CORRECTLY. Need Help Sandy stew "Sandy Mann" wrote: Why not use the SUBTOTAL() function with 9 as the first argument? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "stew" wrote in message ... i HAVE A COLUMN OF EXPENCES THAT IS SUB TOTALLED EVERY 103 ROW USING =SUM(H113:H213),=SUM(H216:H316) etc,etc,tec These sub totals are picked up by =SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),10 3)=8)) All works perfectley until I put a Hyperlink cell in any H ROW AFTER 111. then a #VALUE! COME S UP IN THE =SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),10 3)=8)) BOX. ALLTHE SUB TOTALS ARE FINE AND READ PERFECTLY. My understanding was that =SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),10 3)=8)) only add from the row numbers that divide by 103 and give the remainder of 8 so why is it giving this #VALUE! sYMBOL when all the sub totals are reading correctly If i put a Hyperlink in rows up to 111 then this error does not occur Help Stew |
i HAVE A PROBLEM
Dear Sheeloo
Thank you. Perfect. I do not pretend to fully understand the suttlety of the change I will, as I do, think about what you have indicated. It works. Thanks Again Stew "Sheeloo" wrote: Sorry, I forgot to add --. Pl. use the following =SUMPRODUCT((H111:H25655),--(MOD(ROW(H111:H25655),103)=8)) If you use * then both arrays are multiplied and then passed to SUMPRODUCT. Since there is a #Value! due to the hyperlink result is a #Value!. When you use ',' arrays are passed to SUMPRODUCT which ignores non-numeric values before multiplying and adding... Without '--' one gets and array of TRUE/FALSE which get converted to 1/0 with '--'. "stew" wrote: Hi Thanks for helping. I have substituted your suggested change and now it does not add the subtotals. Can you give me the explanation again ,as I did not get it and I may be able to work it out, however removing the * does not seem to work Stew "Sheeloo" wrote: Not sure my previous post got through..(with detailed explanation) Use =SUMPRODUCT((H111:H25655),(MOD(ROW(H111:H25655),10 3)=8)) "stew" wrote: i HAVE A COLUMN OF EXPENCES THAT IS SUB TOTALLED EVERY 103 ROW USING =SUM(H113:H213),=SUM(H216:H316) etc,etc,tec These sub totals are picked up by =SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),10 3)=8)) All works perfectley until I put a Hyperlink cell in any H ROW AFTER 111. then a #VALUE! COME S UP IN THE =SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),10 3)=8)) BOX. ALLTHE SUB TOTALS ARE FINE AND READ PERFECTLY. My understanding was that =SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),10 3)=8)) only add from the row numbers that divide by 103 and give the remainder of 8 so why is it giving this #VALUE! sYMBOL when all the sub totals are reading correctly If i put a Hyperlink in rows up to 111 then this error does not occur Help Stew |
i HAVE A PROBLEM
You are welcome.
Basically difference is multiplying, say, 9 with #Value! and getting an error Vs converting #Value! to 0 before multiplying to get 0 which is not an error. Next time use SUBTOTAL formula... It ignores any subtotal formulae in the range and gives you the result... syntax =SUBTOTAL(9,A1:A1000) so if you have another subtotal say in A15 it will ignore that entry... 9 indicates SUM... there are other values for 9. Lookup SUBTOTAL in Excel HELP "stew" wrote: Dear Sheeloo Thank you. Perfect. I do not pretend to fully understand the suttlety of the change I will, as I do, think about what you have indicated. It works. Thanks Again Stew "Sheeloo" wrote: Sorry, I forgot to add --. Pl. use the following =SUMPRODUCT((H111:H25655),--(MOD(ROW(H111:H25655),103)=8)) If you use * then both arrays are multiplied and then passed to SUMPRODUCT. Since there is a #Value! due to the hyperlink result is a #Value!. When you use ',' arrays are passed to SUMPRODUCT which ignores non-numeric values before multiplying and adding... Without '--' one gets and array of TRUE/FALSE which get converted to 1/0 with '--'. "stew" wrote: Hi Thanks for helping. I have substituted your suggested change and now it does not add the subtotals. Can you give me the explanation again ,as I did not get it and I may be able to work it out, however removing the * does not seem to work Stew "Sheeloo" wrote: Not sure my previous post got through..(with detailed explanation) Use =SUMPRODUCT((H111:H25655),(MOD(ROW(H111:H25655),10 3)=8)) "stew" wrote: i HAVE A COLUMN OF EXPENCES THAT IS SUB TOTALLED EVERY 103 ROW USING =SUM(H113:H213),=SUM(H216:H316) etc,etc,tec These sub totals are picked up by =SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),10 3)=8)) All works perfectley until I put a Hyperlink cell in any H ROW AFTER 111. then a #VALUE! COME S UP IN THE =SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),10 3)=8)) BOX. ALLTHE SUB TOTALS ARE FINE AND READ PERFECTLY. My understanding was that =SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),10 3)=8)) only add from the row numbers that divide by 103 and give the remainder of 8 so why is it giving this #VALUE! sYMBOL when all the sub totals are reading correctly If i put a Hyperlink in rows up to 111 then this error does not occur Help Stew |
| All times are GMT +1. The time now is 05:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com