i HAVE A PROBLEM

[stew]

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),103)=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),103)=8)) BOX. ALLTHE SUB
TOTALS ARE FINE AND READ PERFECTLY.

My understanding was that
=SUMPRODUCT((H111:H25655)*(MOD(ROW(H111:H25655),103)=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

 

[Sheeloo]

Not sure my previous post got through..(with detailed explanation)

Use
=SUMPRODUCT((H111:H25655),(MOD(ROW(H111:H25655),103)=8))

 

[stew]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sheeloo]

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]

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),103)=8)) is returning
#VALUE!. THE FORMULA IS ONLY SUPPOSED TO PICK OUT THE SUB TOTAL BOXES WHICH
ALL DISPLAY CORRECTLY.

Need Help Sandy

stew

 

[stew]

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]

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