How to make a dynamic adjacent array with Index(Sequence(Counta Function with If(OR, IF(And, Countif/s(?

xaikus506

New Member
Joined
Oct 29, 2022
Messages
46
Office Version
  1. 365
Platform
  1. Windows
Hello Everyone, I would like to ask a question. How can I make the Index(returnvalue,Sequence(Counta(number of rows desired) work with IF(OR, IF(And, Sumif/s( and Countif/s( funtion? Thanks!

Note: the data in column A and B are static data (no formula)

1667699301936.png
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
What, exactly, are you trying to achieve in column C?
 
Upvote 0
What, exactly, are you trying to achieve in column C?
Hi @Peter_SSs, I am trying to achieve the column C will automatically drag down and calculate the last cell with the text/number when the cell is filled in column A or B (any of the column with most COUNTA( value) with the calculation with IF(OR, IF(AND, COUNTIF/S( formulas. Thanks
 
Upvote 0
But can you clarify exactly what the formula is supposed to be calculating? That is, for the sample data, what results would go in C1, C2, C3 and why?
 
Upvote 0
But can you clarify exactly what the formula is supposed to be calculating? That is, for the sample data, what results would go in C1, C2, C3 and why?
Hi @Peter_SSs , I am trying to do when row A or B contains "A", Then Column C will generate a "Yes" else "No" but I want the size of the array to resize itself to the number of rows in A or B (which has the most number of text). What I am trying to do is to avoid dragging down the formula every time I enter a new value to non adjacent and adjacent cells without tables since tables does not work with Functions such as Unique and Filter. Thanks
 
Upvote 0
when row A or B contains "A"
Do you mean when column A or B contains "A"?

See if this does what you want.

22 11 06.xlsm
ABC
1ABYes
2AAYes
3CAYes
4AYes
5BNo
6AAYes
7No
8No
9No
10AYes
11
A
Cell Formulas
RangeFormula
C1:C10C1=BYROW(TAKE(A:B,MAX(XMATCH("*",A:A,2,-1),XMATCH("*",B:B,2,-1))),LAMBDA(rw,IF(ISNUMBER(FIND("|A|","|"&TEXTJOIN("|",,rw)&"|")),"Yes","No")))
Dynamic array formulas.
 
Upvote 0
Solution
Do you mean when column A or B contains "A"?

See if this does what you want.

22 11 06.xlsm
ABC
1ABYes
2AAYes
3CAYes
4AYes
5BNo
6AAYes
7No
8No
9No
10AYes
11
A
Cell Formulas
RangeFormula
C1:C10C1=BYROW(TAKE(A:B,MAX(XMATCH("*",A:A,2,-1),XMATCH("*",B:B,2,-1))),LAMBDA(rw,IF(ISNUMBER(FIND("|A|","|"&TEXTJOIN("|",,rw)&"|")),"Yes","No")))
Dynamic array formulas.
Hi @Peter_SSs, I would like to ask how to do this formula. It is so nested. Thanks by the way for the answer. this is what I am looking for
 
Upvote 0
Hi @Peter_SSs, I would like to ask how to do this formula.
I'm not sure what I can tell you about that. You have to read about the various functions (here is a reasonable place to start), look for and learn from examples where the functions are used in the forum etc.

Thanks by the way for the answer. this is what I am looking for
You're welcome. Thanks for the confirmation. (y)
 
Upvote 0
I'm not sure what I can tell you about that. You have to read about the various functions (here is a reasonable place to start), look for and learn from examples where the functions are used in the forum etc.


You're welcome. Thanks for the confirmation. (y)
Hi @Peter_SSs , Will look into this this will be really helpful. Thanks a lot!
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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