What must the formula look like so that the results correspond to the filter?
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.
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.
Best Answer

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

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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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 :(

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))))" *
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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

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.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

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.

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"))
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!