Complex Count formula

[GoBucks]

I am looking to get a formula that count the number of rows that match some
criteria. The sample table is listed below. I would to have a formula
calculate the # of rows in which values in column B >=3 along with values in
Column C = 0

ID RATING AMOUNT formula match
00304 4.0 (5.0) x
00299 2.5 - x
00069 2.5 2.0
01380 4.0 - x
01729 3.0 - x
01783 2.5 - x
01934 2.0 (1.0)
01901 3.8 (1.0) x
01482 3.0 (1.0) x
02076 2.5 (1.0)

COUNT 5 3 7


B>=3 = 5 rows
C=0 = 3 rows

But if you combine both criteria, there are 7 rows that match. I 'm having a
difficult time figuring our how to write the formula for this. Any
suggestions will be much appreciated.

 

[Pete_UK]

It doesn't look as if any cells in column C contain 0 (or does your -
indicate 0 ? If so, then there are 4 of those).

Generally you would have a formula like this:

=SUMPRODUCT((B2:B10>=3)*(C2:C10=0))

if you want both conditions to be satisfied at the same time before
you count that row, or this:

=SUMPRODUCT((B2:B10>=3)+(C2:C10=0))

if you want either of the conditions to be satisfied.

Hope this helps.

Pete

 

[T. Valko]

I assume the "-" means the cell is empty?

=SUMPRODUCT(SIGN((B2:B11>=3)+(C2:C11=0)))

I don't see a single 0 in

 

[Tom Hutchins]

Here is one way...

=COUNTIF(B2:B10,">=3")+COUNTIF(C2:C10,"=0")-SUMPRODUCT(--(B2:B10>=3),--(C2:C10=0))

Hope this helps,

Hutch

 

[Tom Hutchins]

I think the "-" is a zero in comma format. Your formula is beautiful - thanks
for the lesson in the use of SIGN.

 

[T. Valko]

mark

--
Biff
Microsoft Excel MVP

I assume the "-" means the cell is empty?

=SUMPRODUCT(SIGN((B2:B11>=3)+(C2:C11=0)))

I don't see a single 0 in

 

[T. Valko]

I think the "-" is a zero in comma format.

Yeah, I figured that out after the fact!

Thanks for the feedback!

 

[T. Valko]

I think the "-" is a zero in comma format.

Yeah, I figured that out after the fact!

Thanks for the feedback!

 

[GoBucks]

Thank you for the help!! How would you write the formula if the criteria for
column B is >=3 and <-3.9 along with column C=0??

 

[GoBucks]

Sorry edit to to previous post:

How would you write the formula if the criteria for

 

[Jacob Skaria]

Add one more condition as

=SUMPRODUCT((B2:B11>=3)*(B2:B11<=3.9)+(C2:C11=0))

If this post helps click Yes

 

[David Biddulph]

If you want to use an AND test for the 2 conditions for column B, and then
an OR to combine that with the column C condition, you could change Biff's
=SUMPRODUCT(SIGN((B2:B11>=3)+(C2:C11=0)))
to
=SUMPRODUCT(SIGN(((B2:B11>=3)*(B2:B11<=3.9))+(C2:C11=0)))

 

[GoBucks]

thank you Jacob. I tried the formula and result I recv'd was 7 when I was
looking for 6. There are 3 rows in Column B that match (>=3, <=3.9) and 4
rows in Column C that match (=0). But overall, there are 6 rows total that
match the entire criteria. I can't seem to figure this one out.

 

[Jacob Skaria]

One row satisfy both the conditions.

If this post helps click Yes

 

[Tom Hutchins]

Try this

=SUMPRODUCT(SIGN((B2:B11>=3)*(B2:B11<=3.9)+(C2:C11=0)))

Hope this helps,

Hutch

 

[Jacob Skaria]

Oops...Ignore my post and go with what Tom has suggested. I copy/pasted your
sample data.....
....only meant to add an additional condition to your existing formula...

 

[David Biddulph]

You've left out the SIGN function which Jacob had in his original formula,
and which I think he intended to be in the new one (and which I included in
mine).

 

[David Biddulph]

Correction. The original formula with the SIGN function included was from
Biff, not from Jacob.