Complex Count formula
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
On Nov 4, 12:47*am, GoBucks wrote:
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.
|