0

I have a table (real table, not just looking like one) with multiple columns, one called Date Created and the contents are in the format, e.g., 3. Juni 2023 16:09:12 MESZ.

My goal is to count all entries in column artifact type meeting specific criteria created after a date given in form dd.mm.yyyy to, lets say, get the artifacts created this week.

I get the desired result by adding a colum column1 to table1 with formula =DATEVALUE(LEFT([@[Date Created]];LEN([@[Date Created]])-13)) in combination with =SUM(COUNTIFS(Tabelle1[Artifact Type];{"type1";"type3";"type xy"};Tabelle1[column1];">="&DATEVALUE("14.8.2024"))) in the desired cell.

However, how do I directly get the same result without the step of adding columns? I have no idea how to "evaluate/transform" the entries in Date Created "on the fly".

I am using MS Office 2019 without the possibility of using macros.

Improve this question
7
  • Try: =SUMPRODUCT((Tabelle1[Artifact Type]={"type1";"type3";"type xy"})*(DATEVALUE(LEFT([@[Date Created]];LEN([@[Date Created]])-13))>=DATE(2024;8;14))) Commented Aug 23, 2024 at 11:30
  • @Rory Your Suggestion does not work as I have to add Tabelle1 in front of each referenced cell and it only accepts only one argument for the artifact type. When I use the given ones I get a #N/A-error. Commented Aug 23, 2024 at 12:30
  • @clme What if you remove @ symbols? Commented Aug 23, 2024 at 12:42
  • @bugdrown Sorry, still #N/A. The ugly brute force method would be to calculate the sumproduct 3 times, with summing their results. When I do want to perform the same thing but for, e.g., five different artifacts it gets really ugly really fast with a pain in the back to maintain... Commented Aug 23, 2024 at 13:02
  • You might.be able to do cell transformations and return an aggregate value (e.g., SUM) using Power Query. Commented Aug 23, 2024 at 13:19
Related questions
2191
How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?
577
How to convert a column number (e.g. 127) into an Excel column (e.g. AA)
811
How do I properly clean up Excel interop objects?
Load 5 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.