What must the formula look like so that the results correspond to the filter?

Miranda_1

I would like to recreate the query of a filter with a formula. The formula should display the same number of hits found as the filter.

Formel_or.png

This is what my formula looks like.

=COUNTIFS({Projektliste Bereich 1}; OR(@cell = "on going"; @cell = "on hold"; @cell = "finished"; @cell = "abschließen"; @cell = "stopped"; @cell = "Teil von Großprojekt")),{Projektnummer}; CONTAINS("2022"; @cell); {Projektliste Bereich 5}; OR(@cell >=DATE(2022; 1; 1); @cell <=DATE(2022; 12; 31))

Unfortunately, the result is "UNPARSEABLE".

Thanks for you help.

Miranda_1

now i have a formula that fits :-) many thanks to smartsheet support

=COUNTIFS({Projektnummer}; CONTAINS("2022"; @cell); {Status}; OR(@cell = "on going"; @cell = "on hold"; @cell = "finished"; @cell = "abschliessen"; @cell = "stopped"; @cell = "Teil von Großprojekt")) + COUNTIFS({Projektnummer}; NOT(CONTAINS("2022"; @cell)); {Termin Projektstart}; AND(@cell >= DATE(2022; 1; 1); @cell <= DATE(2022; 12; 31)); {Status}; OR(@cell = "on going"; @cell = "on hold"; @cell = "finished"; @cell = "abschliessen"; @cell = "stopped"; @cell = "Teil von Großprojekt"))

Jeff Reisman

@Miranda_1

I think you have an extra close parentheses and a comma instead of a semicolon right here:

Take one parentheses away and replace the comma with a semicolon.

Miranda_1

you are right, now the formula works. thank you very much!

however, the result does not correspond to the result from the filter. the filter finds 13 hits, the formula only 6.

=COUNTIFS({Projektliste Bereich 1}; OR(@cell = "on going"; @cell = "on hold"; @cell = "finished"; @cell = "abschliessen"; @cell = "stopped"; @cell = "Teil von Großprojekt"); {Projektnummer}; CONTAINS("2022"; @cell); {Projektliste Bereich 5}; OR(@cell >= DATE(2022; 1; 1); @cell <= DATE(2022; 12; 31)))

{Projektliste Bereich 1} corresponds to "Status"

{Projektnummer} corresponds to "Projektnummer"

{Projektliste Bereich 5} corresponds to "Termin Projektstart"

there must still be an error in the composition of the formula :-(

Jeff Reisman

@Miranda_1

Many people don't realize that AND is implied in the COUNTIFS/SUMIFS functions - meaning that unless specifically changed by OR, each criteria listed must be met for the row to be counted.

Your filter logic says: where Termin Projektstart is between 1/1/22 and 31/12/22, OR where Projektnummer contains 2022, AND where the Status is one of these values.

Your COUNTIFS logic says: Where the status is one of these values, AND where Projektnummer contains 2022, AND where Termin Projektstart is greater than or equal to 1/1/22 OR less than or equal to 31/12/22. Using OR with dates like this actually ends up meaning any date whatsoever, because all dates are either >= 1/1/22 OR <= 31/12/22.

The COUNTIFS ends up finding fewer rows because all the rows MUST contain 2022 in the Projektnummer column.

Try this one. (It may not work, due to the second OR being applied to two different ranges, but worth a try.)

=COUNTIFS({Projektliste Bereich 1}; OR(@cell = "on going"; @cell = "on hold"; @cell = "finished"; @cell = "abschliessen"; @cell = "stopped"; @cell = "Teil von Großprojekt"); OR({Projektnummer}; CONTAINS("2022"; @cell); {Projektliste Bereich 5}; AND(@cell >= DATE(2022; 1; 1); @cell <= DATE(2022; 12; 31))))

*Make sure the parentheses colors match up. The first open parentheses after COUNTIFS( should be the same color as the last end parentheses at the end of "DATE(2022; 12; 31))))" *

Miranda_1

Yes, thanks, that's the right idea, to use AND and OR correctly. Unfortunately, the formula doesn't quite work yet. I get back "#INVALID DATA TYPE"

=COUNTIFS({Projektliste Bereich 1}; OR(@cell = "on going"; @cell = "on hold"; @cell = "finished"; @cell = "abschliessen"; @cell = "stopped"; @cell = "Teil von Großprojekt"); OR({Projektnummer}; CONTAINS("2022"; @cell); {Projektliste Bereich 5}; AND(@cell >= DATE(2022; 1; 1); @cell <= DATE(2022; 12; 31))))

Jeff Reisman

@Miranda_1

I think you will need to do separate COUNTIFS and add them together:

=COUNTIFS({Projektliste Bereich 1}; OR(@cell = "on going"; @cell = "on hold"; @cell = "finished"; @cell = "abschliessen"; @cell = "stopped"; @cell = "Teil von Großprojekt"); {Projektnummer}; CONTAINS("2022"; @cell); {Projektliste Bereich 5}; AND(@cell >= DATE(2022; 1; 1); @cell <= DATE(2022; 12; 31))) + COUNTIFS({Projektliste Bereich 1}; OR(@cell = "on going"; @cell = "on hold"; @cell = "finished"; @cell = "abschliessen"; @cell = "stopped"; @cell = "Teil von Großprojekt"); {Projektnummer}; CONTAINS("2022"; @cell); {Projektliste Bereich 5}; AND(@cell <= DATE(2022; 1; 1); @cell >= DATE(2022; 12; 31))) + COUNTIFS({Projektliste Bereich 1}; OR(@cell = "on going"; @cell = "on hold"; @cell = "finished"; @cell = "abschliessen"; @cell = "stopped"; @cell = "Teil von Großprojekt"); {Projektnummer}; NOT(CONTAINS("2022"; @cell)); {Projektliste Bereich 5}; AND(@cell >= DATE(2022; 1; 1); @cell <= DATE(2022; 12; 31)))

The first one counts rows where the Projektnummer contains 2022 AND where the Termin Projektstart falls inside your date range.

The second one counts rows where the Projektnummer contains 2022 AND where Termin Projektstart falls OUTSIDE your date range.

The third one counts rows the Projektnummer DOES NOT CONTAIN 2022 AND where the Termin Projektstart falls inside your date range.

None of those three sets should overlap each other, so you should get the correct count.

Miranda_1

that actually sounds quite logical. too bad, that could have worked. but the result is now 9.

something still seems to be missing. I have to think about the logic again.

Miranda_1

now i have a formula that fits :-) many thanks to smartsheet support

=COUNTIFS({Projektnummer}; CONTAINS("2022"; @cell); {Status}; OR(@cell = "on going"; @cell = "on hold"; @cell = "finished"; @cell = "abschliessen"; @cell = "stopped"; @cell = "Teil von Großprojekt")) + COUNTIFS({Projektnummer}; NOT(CONTAINS("2022"; @cell)); {Termin Projektstart}; AND(@cell >= DATE(2022; 1; 1); @cell <= DATE(2022; 12; 31)); {Status}; OR(@cell = "on going"; @cell = "on hold"; @cell = "finished"; @cell = "abschliessen"; @cell = "stopped"; @cell = "Teil von Großprojekt"))