Theoretically speaking...

D

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
21,620
Office Version
  1. 365
Platform
  1. Windows
Hi everyone!

I'm trying to understand why the following formula doesn't work...

{=SUM(--(T(OFFSET(A1,ROW(INDIRECT({"A3","A5","A8"}))-1,0))="x"))}

It should count the number of specifically chosen cells in Column A that contains 'x'.

For some reason, it doesn't like ROW(INDIRECT(...), even though on its own it returns an array of numbers which should feed the second arguement of the OFFSET function.

If I replace ROW(INDIRECT(...) with {3,5,8}, the formula returns the correct answer.

While I know that using ROW(INDIRECT(...) is not really practical, I'd like to understand why it doesn't work.

Thanks for your help!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
I think ROW() does not admit array objects. In that formula, it would expect INDIRECT() to return a single range object.
 
Upvote 0
Hi Aladin!

Oh I see. So that would explain why only the first reference in INDIRECT is evaluated and not the others.

I had a feeling there would be a logical explanation. :)

That's great. Thanks Aladin. Much appreciated.

Cheers!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
INDIRECT with condition or FILTER formula
Replies
9
Views
2K
Fluff
Fluff
afrazier1221
  • Solved
VBA adding symbols, disabling the intended code. Please help.
Replies
1
Views
296
afrazier1221
afrazier1221
A
  • Solved
Transpose Formula based on Criteria
Replies
5
Views
471
Fluff
Fluff
jerry12302
  • Question
Using Indirect(Address instead of actual range in an Index,Match(TRUE formula
Replies
9
Views
947
Fluff
Fluff
A
  • Solved
Finding a unique value in a range and returning the value in another column of the same row, without offset / slow formulae.
Replies
8
Views
1K
AaronPMH
A

Forum statistics

Threads
1,215,873
Messages
6,127,459
Members
449,383
Latest member
DonnaRisso

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top