ExcelBanter - Count IF

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Count IF (https://www.excelbanter.com/excel-discussion-misc-queries/247070-count-if.html)

I need a formula for the following:

If Col E on Sheet1 = "Canada" (B5), than count if Col O on Sheet1 if between
..75 & .999

Someone had tried helping earlier with a similar formula, but it didn't work.

=SUMPRODUCT((Sheet1!E2:E5000="Canada")*(Sheet1!O2: O5000=.75)*(Sheet1!O2:O5000<=.999))

Presuming you want .75 and .999 included.

"KC" wrote:

I need a formula for the following:

If Col E on Sheet1 = "Canada" (B5), than count if Col O on Sheet1 if between
.75 & .999

Someone had tried helping earlier with a similar formula, but it didn't work.

That gave me a #DIV/0! error. I'm trying to count the insances a cell in
that range is between .75 and .999.

"Sean Timmons" wrote:

=SUMPRODUCT((Sheet1!E2:E5000="Canada")*(Sheet1!O2: O5000=.75)*(Sheet1!O2:O5000<=.999))

Presuming you want .75 and .999 included.

"KC" wrote:

I need a formula for the following:

If Col E on Sheet1 = "Canada" (B5), than count if Col O on Sheet1 if between
.75 & .999

Someone had tried helping earlier with a similar formula, but it didn't work.

Did you use this formula or did you use this formula as a divisor in a different
formula?

If you used this formula as-is, then you have a #div/0 error in one of those
ranges (E2:e5000 or o2:o5000). (Remember to look in hidden rows, too!)

I'd clean up those errors and continue to use Sean's suggestion.

If you used Sean's formula as a divisor, then you'll want to test to see if it's
0 before you use it:

=if(sean'sformula=0,"Nothing found",(someotherformula)/sean'sformula))

KC wrote:

That gave me a #DIV/0! error. I'm trying to count the insances a cell in
that range is between .75 and .999.

"Sean Timmons" wrote:

=SUMPRODUCT((Sheet1!E2:E5000="Canada")*(Sheet1!O2: O5000=.75)*(Sheet1!O2:O5000<=.999))

Presuming you want .75 and .999 included.

"KC" wrote:

I need a formula for the following:

If Col E on Sheet1 = "Canada" (B5), than count if Col O on Sheet1 if between
.75 & .999

Someone had tried helping earlier with a similar formula, but it didn't work.

--

Dave Peterson