Criteria that include empty entries

[eeigor]

Suggest a criterion so as not to lose the amount when the Date field is empty.
test-empty-strings-in-range.ods (28.1 KB)
UPDATED: The example has been updated below (see test-empty-strings-in-range (1).ods).

“>-1” does not include empty entries, and “<>-1” does, but only if both StartDate and EndDate fields are empty.
An example is attached.

That’s a good formula too:
ROW(INDIRECT(“1:”&ROWS(Data)))
but Multiple Operations rejects it completely. It doesn’t even work like this (simplified): ROW(“1:515”)
All other ways are being considered. How not to lose an empty string. There is a separate position in my table for such entries: (empty). But the amount in these entries should be indicated.
Make up your criteria on the proposed example please.

Sorry, file updated

[sokol92]

The sum for rows where the date is empty can be calculated as follows:

=SUMIF(DateColumn;"";AmountColumn)

[eeigor]

It is necessary to work with the StartDate and EndDate criteria for the entire dataset and take into account possible empty entries. StartDate and EndDate can be empty: either one or both.
A substitution table with two inputs is filled in (not represented in the file) using Multiple operations. Nothing gets into the line (empty).

UPD:
I am not ready to bring the whole application (very large), and asked one specific question. But I can post a picture. The cursor is located in the cell with the lost record.

---

Specifically. The StartDate and EndDate fields are empty. The counter shows the number of entries: 515. Enter the minimum date in the StartDate field from the drop-down list. The counter loses one record. Why?

UPD:
“(empty)” row and column serve as a trap. After correcting data filling errors, these row and column in the table can be hidden when printing.

[Lupp]

I didn’t study the described case (image) in detail, but want to hint:

The usage of criteria shouldn’t be “necessary”. Personally I regard the citeria concept basically outdated and obsolete. SUMPRODUCT() mostly can handle the case, and even may allow for clearer or “more transparent” formulas. In addition it may avoid the need of automatic conversions string<—>number in the background and the related disadvantages.
In specific you can’t distinguish between ="" and ISBLANK(...) without annoying complications.

An example not very closely related to the question, but showing what I mean, you find attached.

disask69728SUNPRODUCTusedInPlaceOfCriteriaFunctions.ods (33.3 KB)

[eeigor]

I ask you to help solve a specific problem: to make the SUMIFS and COUNTIFS functions count the same way as the SUMPRODUCT function counts. The example is attached anew.
SUMPRODUCT doesn’t work with regexes, and the first two functions do and I need them. It is necessary to get 515 records in the proposed conditions (DataColumn contains an empty string, StartDate is empty). Maybe add:
+COUNTBLANK(DateColumn)

---

UPD: File removed and added below with fixes

[erAck]

Add this to your COUNTIFS() formula in I4:

+IF(ISBLANK(StartDate);COUNTBLANK(DateColumn);0)

Btw, your Validity formula uses a locale dependent (German) date format DD.MM.YYYY, which makes things fail in other locales (as it did for me) because then selecting the entry produces cell content of type text instead of type numeric date. Use YYYY-MM-DD instead, which produces a valid date cell content in any locale.

[eeigor]

@erAck, I corrected the example, changed the date format.
But this is not exactly what I need. I am losing data from the last record ($1 here). Re-watch the example please. This is clearly a bug. SUMIFS or COUNTIFS and SUMPRODUCT handle the expression differently.
It’s not about fixing the row count, but about correct calculations. All arrays are the same height, but the functions handle the empty string differently, as I said.

SUMIFS or COUNTIFS (both lose an empty record):
DateColumn;">="&IF(ISBLANK(StartDate);0;StartDate)

SUMPRODUCT (includes an empty record):
DateColumn>=IF(ISBLANK(StartDate);0;StartDate)
NOTE: An empty value gives a comparison with 0 or zero length string ("").

---

test-empty-strings-in-range (1).ods (28.7 KB)

[karolus]

Thats not a bug, thats missusing Formulas for the wrong purpose.
A pivot-Table produces sensefull outputs for any repeated Date ( lets them empty )
test_pivot_table_with_recognize_kriteria.ods (29.4 KB)

[eeigor]

@karolus, it was not about that.

@karolus

Thats not a bug, thats missusing Formulas for the wrong purpose.

How is it: why do different functions produce different results when evaluating the same expression?

And the pivot table counts everything because the option is checked.

But even without this option, the pivot table will not lose anything, but will put it in the (empty) category.
But: SUMIFS or COUNTIFS both lose an empty record.

Pivot table and SUMPRODUCT function are not suitable for me because I am using complex filter criteria using regular expressions. SUMIFS and COUNTIFS do this. And if the criterion is numerical, and even with a comparison operator (e.g., >1.5), these two new functions do an excellent job.

The bug is completely localized and I described it above in the answer to @erAck’s post.

[erAck]

@eeigor

This is clearly a bug

COUNTIF(), SUMIF(), COUNTIFS() and SUMIFS() work on cells only with content if the Criteria argument is not "=" (without a comparison value). Gnumeric agrees, does Excel?

SUMPRODUCT() works on arrays of cell contents, in which an empty cell in numeric context is 0.

[eeigor]

@erAck, thanks, I got out. But, you see, these are very subtle differences.
I’ll check it in Excel later.

I have provided information to the user about the need to fill in all the data. Plus, the pivot table is always on guard and serves to monitor the result.
Advanced Filter + Pivot Table + Multiple Operations = Valid Result in any case
These are completely different tools in terms of usability, and you have to use them all at the same time.