How to use a formula in CountIfs criteria field?

[JenniferMurphy]

Is there a way that I can use a formula other than a simple expression like "<0" in the criteria fields of functions like CountIfs?

In this table, column C has timestamps. I'd like to count how many have times before noon. In this example, there are 3 (C5, C7, & C9).

The expression "mod(C5,1)" (E5) will return the time portion of the date-time value. So if I can use that in the criteria field, I can get the count that I need as illustrated in F6. But all of the ways I tried either get errors or return zero.

 

[jasonb75]

You can use formulas as criteria in the format of "<="&MOD(....) but it only works to create the criteria, not to manipulate the range that you're counting.

For what you need, you would have to use sumproduct, countifs is not capable.

=SUMPRODUCT(--(MOD(C5:C9,1)<=0.5))

 

[JenniferMurphy]

You can use formulas as criteria in the format of "<="&MOD(....) but it only works to create the criteria, not to manipulate the range that you're counting.

Got it, thanks.

For what you need, you would have to use sumproduct, countifs is not capable.

=SUMPRODUCT(--(MOD(C5:C9,1)<=0.5))

Your expression works. Thanks.

That's an interesting application of sumproduct. I guess if only one range is supplied, there is nothing to multiply so it is just the same as sum(), right?

I am curious about the "--". Why is that necessary? I tried it without the "--". It returns zero. I also tried replacing the "--" with "+", which mathematically should be the same as "--", no? But that also returns zero.

Is it magic?

 

[jasonb75]

I guess if only one range is supplied, there is nothing to multiply so it is just the same as sum(), right?

Yes, but if you use SUM() you would have to array confirm it. It might work without using 365 dynamic arrays, but not in older versions.

I am curious about the "--". Why is that necessary?

The single array returns TRUE and FALSE instead of 1 and 0, so -- is needed to coerce the numeric values When you multiply 2 or more arrays, this is happens automatically. I agree with you that logically, + is the same as -- but for whatever reason it doesn't work.
It also works with 0+ 0- or 1* at the beginning, or +0 -0 *1 or /1 at the end, there are probably a few others as well. While there are exceptions, for most people -- appears to be the accepted normal.