EN VI

Google-sheets - Get array as output in specific function?

How to Google-sheets - Get array as output in specific function

I was working on my spreadsheet to keep track of my goals. I stuck on some strange problem I can't find any fix for that, not so advanced in google sheets yet :(

I made test sheet just to show problem in isolation. https://docs.google.com/spreadsheets/d/19xUOeLoXTPH3heVFMssya5sh8ZeOaIlIBpHnypaDx-A/edit?usp=sharing

I need to count amount of specific task in the arrays(weekdays) by specific value.

I have weekinfo which checks what day is on the calendar by position - in the test table I just made it equals 2 And then I have IFS function which assigns specific array by the weekinfo number - SA,SB,SC which are just arrays under variable $A$3:$A.

But for some reason any IFS - countifs, ifs etc - they can't give arrays as outcome, they just can't process them and they just give error that ifs has mismatched range sizes.

Does anyone know what might be a solution for that?

+	A	B	C	E	F
1
2	array1	array2	array3
3	test	5	test
4	test	6	test
5	3	test	test
6
7				0	- should be 1

formula (tried in Cell_E7)

=LET(
SA,A3:A5,
SB,B3:B5,
SC,C3:C5,
task,"test",
weekinfo,2,
array,IFS(weekinfo=1,SA,weekinfo=2,SB,weekinfo,SC),
COUNTIF(array,task)
)

Solution:

You may try:

=let(
     data,A3:C5,
     task,"test",
     weekinfo,2,
     countif(choosecols(data,weekinfo),task))

OR with a slight modification to your formula

=LET(
SA,A3:A5,
SB,B3:B5,
SC,C3:C5,
task,"test",
weekinfo,2,
array,IF(weekinfo=1,SA,IF(weekinfo=2,SB,IF(weekinfo,SC))),
COUNTIF(array,task)
)