Problem referring to specific cells

[koenigs]

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

 

[Weaver]

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

 

[Weaver]

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

 

[Weaver]

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

 

[koenigs]

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