Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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.

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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 D 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)
)

enter image description here

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading