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