Ask Your Question
0

What's wrong with this formula?

asked 2015-12-08 17:52:55 +0200

redart gravatar image

updated 2015-12-10 20:56:26 +0200

Alex Kemp gravatar image

Hi, I have a standard filter applied to one or more columns and need to count the resulting visible cells in another column that equal a certain value. In Excel this can be done with the following formula:-

=SUMPRODUCT(SUBTOTAL(3,OFFSET(BH10,ROW(BH10:BH1000)-ROW(BH10),0))*(BH10:BH1000="T"))

This counts the number of visible cells in column BH that equal "T". The result happens to be 29, however when I use this same formula in Calc on the same spreadsheet I get 270538 !. Anyone know what could be going on here?. Thanks. p.s. I should add that it makes no difference if the formula is entered as an array formula or not. LO 4.4.6.3 on Linux.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2015-12-10 02:09:47 +0200

erAck gravatar image

Please submit a bug report at https://bugs.documentfoundation.org/ Apparently Excel treats an array of OFFSET() results differently if it occurs as an argument to SUBTOTAL().

edit flag offensive delete link more

Comments

Thanks for the suggestion and link to Bugzilla. Bit of a delay but bug report now submitted.

redart gravatar imageredart ( 2015-12-19 15:14:53 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-12-08 17:47:10 +0200

Seen: 132 times

Last updated: Dec 10 '15