# How to use a formula in CountIfs criteria field?

#### JenniferMurphy

##### Well-known Member
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.

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### jasonb75

##### Well-known Member
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

##### Well-known Member
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))

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

##### Well-known Member
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.

Replies
3
Views
45
Replies
4
Views
74
Replies
10
Views
709
Replies
3
Views
126
Replies
6
Views
468

1,127,870
Messages
5,627,369
Members
416,245
Latest member
Xterminat

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back