How to use a formula in CountIfs criteria field?

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,438
Office Version
  1. 365
Platform
  1. Windows
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).
CountIfs Criteria Formulas.jpg


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.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,432
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 23, 2011
Messages
1,438
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,432
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,395
Messages
5,624,463
Members
416,029
Latest member
CSM1

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top