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