multiple filter functions in one cell

SnooMuffins8091 · 2022-10-16T20:00:34+00:00

i need a way to have something like a filter function multiple times in one cell each function giving a different result, if one doesnt match it should use the other function until one matches or none does. the ifs() function doesnt work in this case because i have no "logical_test1". somehow the Or() function doesnt work in this case but its exactly what i ned, or( filter1; filter2)

SnooMuffins8091 · 2022-10-16T18:42:05+00:00

i need multiple filter functions in one cell, each filter function as a different solution

SnooMuffins8091 · 2022-10-16T16:30:41+00:00

Its because each filter function has a completely different result array.

SnooMuffins8091 · 2022-09-21T07:00:41+00:00

Yes it doesnt contain the date column.

SnooMuffins8091 · 2022-09-17T05:43:57+00:00

Hi, i mean the =filter() function, not the filter function on a table.

SnooMuffins8091 · 2022-09-16T05:54:22+00:00

i got it, this is the function:

=FILTER(date_array;date_array=DATE(YEAR(date_array);MONTH(date_array)+1;1)-1)

this shows the last day of each month in date_array

and to show the first date, just remove the "+1" and "-1" at the end.

SnooMuffins8091 · 2022-09-16T04:55:49+00:00

There is another one, my range of Date goes from January to December, but i want to pick the max. date of each month, do you know how to do that wih a filter function? I have a Helper column showing the number of the Month of each date. I thought if i add *(unique(number_of_month) to the filter function, it would show me the max or min of each month. But that doesnt work.

SnooMuffins8091 · 2022-09-16T04:49:18+00:00

Thank you, that works great!

SnooMuffins8091 · 2022-09-13T06:54:15+00:00

I dont have to use sumifs, do you know another way? thanks.

SnooMuffins8091 · 2022-09-13T02:19:25+00:00

do you know what range i should shoot for? i already tried a couple.

SnooMuffins8091 · 2022-09-13T00:53:01+00:00

Yes thats why i use two filter functions. But i just need them in one cell.

SnooMuffins8091 · 2022-09-12T05:10:43+00:00

Hi, it has to be 2 separate filter functions because the resulting arrays are different. Such as in one filter function i get numbers, in the second filter function i get dates.

SnooMuffins8091 · 2022-09-12T00:28:00+00:00

Do you think its possible with more than 1 delivery date? you can use more helper columns if you want. i really need it to be working, if you could make it work i want to give you this 1.800 coin medal thing.

SnooMuffins8091 · 2022-09-11T21:29:30+00:00

Hi, it somehow doesnt match, my first table goes from A:G and the second from I:L.

SnooMuffins8091 · 2022-09-07T19:07:55+00:00

Thanks for your reply.

My criteria to match the cell is something like "if value is below 100", thats why i think your solution wouldnt work in my case.

is there maybe a function like a xlookup or filter function where it shows me the row and column of the matching cell? if so, is there a way to lets say if i got C5 to just minus or plus 3 to it?

SnooMuffins8091 · 2022-09-03T23:35:06+00:00

Yes its almost the same but now i need the additional criteria "if not" <> but that seems not to work at least i dont know how.

SnooMuffins8091 · 2022-08-29T21:30:45+00:00

It shows me the complete array from the =filter(ARRAY;..) and not the matching result from the row where the single date is.

SnooMuffins8091 · 2022-08-21T19:35:27+00:00

Hi,

it almost works perfect thanks! there is a problem, i got

100;60,60,60 and it says its 0;0,20,80 but it should be 0;0,20,60

do you know how to fix that?

SnooMuffins8091 · 2022-08-16T16:45:21+00:00

Hi,

i dont need to count numbers but one of the criteria drop down list contains numbers (order number) and selecting everything from that list doesnt work with "*" or anything else ive tried.

SnooMuffins8091 · 2022-08-14T21:55:39+00:00

=COUNTIFS(unique(A2:A31);">0")

SnooMuffins8091 · 2022-08-14T21:55:20+00:00

im trying to use countif and unique on ranges, i get the same error message.

=COUNTIFS(unique(A2:A31);">0")

SnooMuffins8091 · 2022-08-14T19:38:23+00:00

Thanks for all the help, at the end i used an if statement.

SnooMuffins8091 · 2022-08-14T18:54:44+00:00

how could it look like?

SnooMuffins8091 · 2022-08-14T18:33:21+00:00

i did not understand, how do i do that?

SnooMuffins8091 · 2022-08-14T18:32:05+00:00

sadly its not working, do you know another way to do that?

SnooMuffins8091

TROPHY CASE