COUNTIFS not producing expected result

It's difficult to interpret the formula without seeing the parts of the table referenced. The highlighting of cells in the large table does not appear to match the highlighting in the formula.

Syntax for COUNTIFS:

COUNTIFS(test-values, condition, test-values…, condition…)

COUNTIFS is generally placed in a single cell if it is doing a count of items fitting a single set of conditions.

It is filled down a column if counts of several similar sets are being done. in this case, for example if you were doing counts for five different vssid codes, you might list the codes in five rows of a single column and use a relative reference to the cell in that column on the same row as the formula as the condition for one of the test-values, condition pairs. The formula would then be entered into the row containing the first code, then filled down four rows to create one copy referencing each of the codes.

Your formula has two test-values, condition pairs:

1. vssid, vssid lbleakly2
2. T, T114 < DATE(Table 2::$B$@,Table 2::$C$2, Table 2::$D$2)

See the comments above regarding the first pair.

The second pair has a couple of errors.

COUNTIFS looks at each of the test-values in column T and compares that value with the value constructed by DATE from the contents of cells B2,C2 and D2 on Table 2. The reference to cell T114 is not needed and should be removed.

The expression of the condition must be presented as text, but within that text, numbers needs to see any cell references as cell references, not as a text string. That' a tough concept to catch on to if you're not making frequent use of it, so it's not surprising when errors occur.

The values in column T must be Date&Time values, not text. If the alignment setting on that column is Automatic, Numbers will align numbers and number-like values (including Date&Time values) to the right edge of the cell, and will align text values to the left. The left alignment of values in column T means either you have set the alignment to Left, or the values in those cells are text, not dates, and cannot be compared with the Date&Time value constructed by the Date function.

Corrected version of this pair: T, "<"&DATE(Table 2::$B$@,Table 2::$C$2, Table 2::$D$2)

Here's an example using separate tables to record the data (Table 1), do the counts (Table 2) and construct the threshold date (Table 3)

Formula list below.

Table 1: Data only. no formulas.

Table 2: Two formulas

A2: =Table 3::A2

Copies the date from Table 3.

C2: =COUNTIFS(Table 1::$A,B,Table 1::$B,"<"&A$2)

Does the count.

Fill down to C5

Table 3: =DATE(A3,A4,A5)

Constructs the Date&Time value.

(Same as your formula, modified for a vertical list of yyyy, m, d)

Regards,

Barry

Oops, I meant to type COUNTIFS. I hope I didn't add to confusion😟.

I assume you are counting things, not trying to add things up? The two work in similar ways, with column-condition pairs. With SUMIFS (unlike what I wrote) you need to start with a reference to the column you are summing, followed by column-condition pairs. With COUNTIF you don't have that initial column because you aren't summing anything. If you haven't seen it yet you can read about COUNTIFS here (you can also get there via Help > Formulas and Functions Help in your menu. Explanations in many different languages are available there.

In general I recommend keeping the structure of the formula simple. If you can post a screenshot with more of your table(s) showing what you are trying to do, lots of experts here can help you out.

SG

Crucial details are missing from your screenshot, such as the setup of your columns in the table(s).

However, it does seem as if your formula is too complicated. COUNTIFS typically simply contains a series of column-condition pairs.

=SUMIFS(<a column of values>,<a condition to match them on>,<another column of values>,<a condition to match those on> .....)

SG

"I can't use SUMIFS because the data in those columns is not a (plain) number, date or duration... Hmmm."

I don't think you want to use SUMIFS in any case. You're attempting to COUNT all instances where all conditions report TRUE, not to total the values in a particular column when all conditions are report TRUE.

SG noted that he'd had a wetware glitch (aka a typo) and wrote SUMIFS in the formula where he meant to write COUNTIFS. Substitute that function for SUMIFS in your formula, and see if that's the only correction you need.

Regards,

Barry