# Problem referring to specific cells

#### koenigs

##### New Member
Hi guys. I'm having problems working out how to use a COUNTIF statement to tell me how many cells in a given range satisfy a certain condition that is somewhat more complicated than those that I'm used to (e.g >5, 7, "apples"). Specifically, I wan't to know how many cells are solutions to x=2(mod7). I'm wondering if there is a way to say something like COUNTIF(range, 'some expression involving x where x refers to all cells in the given range'), so I could do things like COUNTIF(range, x^2+4x=0) to tell me how many cells satisfy that equation. Hopefully the above will make some sense to someone and they'll be able to help! Thanks.

Koenigs

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Are you using dates?

If so,

=SUMPRODUCT(--(WEEKDAY(\$I\$4:\$I\$30)=2))

might work. Substitute your own range obviously, but don't use whole columns unless you're on 2007

PS, I think using sumproduct would allow you to use more complex comparisons.

I'd be interested to see if this works for your second question - I haven't got a decent set of data to test it on!

=SUMPRODUCT(--(((\$I\$4:\$I\$30)^2+4*(\$I\$4:\$I\$30))=0))

Thanks for your help. Yes, I was using dates and what you propose does seem to work for the latter problem as well!

Replies
7
Views
161
Replies
10
Views
352
Replies
6
Views
149
Replies
0
Views
186
Replies
14
Views
718

1,203,120
Messages
6,053,631
Members
444,674
Latest member
Fieldy1999

### 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