CountIf Function - require range of criterea

[Guest]

Excel 2003
Looking for a function that will handle:
Count AND a range of criterea

Something like:
=COUNTIF(X58:X73,">23 & <31")
=COUNTIF(X58:X73,">30,<58")
=COUNTIF(X58:X73,">56,<73")
(but this doesn't work)

any ideas?
Thanks

 

[Guest]

A couple of ways

=COUNTIF(X58:X73,">23")-COUNTIF(X58:X73,">=31")

=SUMPRODUCT(--(X58:X73>23),--(X58:X73<31))

then adapt the rest of the conditions using the same technique


Regards,

Peo Sjoblom

 

[Guest]

Sounds like you want SUMPRODUCT.

=SUMPRODUCT((X58:X73>23)*(X58:X73<31))

Check this out: http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[Aladin Akyurek]

A set of 2 critera that can be taken together as an interval...

=COUNTIF(X58:X73,">"&Y2)-COUNTIF(X58:X73,">="&Z2)

where Y2 <= Z2 like with 23 and 31, repectively, we get a count of
numbers that fall in the interval specified by "> 23 and < 31".

 

[Guest]

Thanks ALL:
I'll try each method so it will hopefully drill in my head somewhere.
I'll have to come back after supper to try them out.

The prompt response is greatly appreciated.
John F.

 

[Guest]

I'm having trouble implementing the various formulas I'm seeing posted. I
think I want the same thing many others are asking for but must be doing
something wrong:

I used several including:
=COUNTIF(A1:A5,">"&0)-COUNTIF(A1:A5,"<"&10)
RANGE CONTENTS:
2 | 5 | 17 | 6 | 29
So, I would expect my formula result to equal 3.

How do I acheive this?
Thanks in advance!

SJP~

 

[Guest]

Hi
Try:
=COUNTIF(A1:A5,">"&0)-COUNTIF(A1:A5,">"&10)
You want a count of all and then take off those that are bigger than 10, I
guess.

 

[Guest]

Ahhhhh yes!! Thank you...my little brain just was simply not thinking in that
way!! It seems so simple when I look at this now but I was determined to try
to look in-between rather than subtract from a total!

I may be back for more help later ;o)

Thanks again,
Steve

 

[Guest]

Glad to help! Thanks for the feedback. In future, it may help you to know
that you can select parts of the formula, in the formula bar, and hit F9.
Excel will then show you what the result of that bit of the formula equates
to. To exit, hit Esc.

 

[Guest]

That is helpful...I didn't know that!

Also, I'm back for more help...

Now I was hoping to capture and sum cells that fall within the criteria
found from the previous formula results. For instance, once it has been
determined that there are 3 cells that were returned by the formula below, I
would like to capture the value in a cell on the same row (a few columns
over) that has a cost there and I want to sum the costs of those 3. I hope
I'm being clear...if not let me know and I will try to do better.

Thanks!

 

[Biff]

Hi!

You need a different function to do that:

So, what you want to do is sum values in another column that correspond to
values in column A that are >0 and <10.

If that's what you want:

=SUMPRODUCT(--(A1:A10>0),--(A1:A10<10),C1:C10)

Biff