I'm using Excel 2003 SP2. I need a formula that will tell me how many times
tests passed across multiple platforms. For instance, I have two columns; A
and B. Column A contains multiple platforms, ie: oracle, unix, mssql2K5.
Column B contains the results pass, fail, blocked.

ColA ColB
mssql2k5 pass
oracle pass
unix fail
mssql2k5 fail
oracle pass
unix fail
mssql2k5 blocked
oracle blocked
unix blocked


I can use COUNTIF and determine the number of pass/fail/blocked cases, and I
can determine the number of platforms tested on. What I can't seem to get is
the number of cases passed, etc. on mssql2K5, etc.

What I want is something like =COUNTIF(A:A,"mssql2k5") and
COUNTIF(B:B,"pass"). I tried using =COUNTIF(AND(AA:,"mssql2k5"),B:B,"pass")
, and the other way around, but that didn't work. Is this type of formula
possible?

=SUMPRODUCT(--(A1:A100="mssql2k5"),--(B1:B100="pass"))

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"pkl" wrote in message
...
I'm using Excel 2003 SP2. I need a formula that will tell me how many
times
tests passed across multiple platforms. For instance, I have two columns;
A
and B. Column A contains multiple platforms, ie: oracle, unix, mssql2K5.
Column B contains the results pass, fail, blocked.

ColA ColB
mssql2k5 pass
oracle pass
unix fail
mssql2k5 fail
oracle pass
unix fail
mssql2k5 blocked
oracle blocked
unix blocked


I can use COUNTIF and determine the number of pass/fail/blocked cases, and
I
can determine the number of platforms tested on. What I can't seem to get
is
the number of cases passed, etc. on mssql2K5, etc.

What I want is something like =COUNTIF(A:A,"mssql2k5") and
COUNTIF(B:B,"pass"). I tried using
=COUNTIF(AND(AA:,"mssql2k5"),B:B,"pass")
, and the other way around, but that didn't work. Is this type of formula
possible?

Hi PKL,

you could use sumproduct also as:

sumproduct(A2:A50="mssql2k5")*(b2:b50="pass"))

note that you couldn't use entire column like A:A

Regards from Brazil and thanks for the feedback
Marcelo



"pkl" escreveu:

I'm using Excel 2003 SP2. I need a formula that will tell me how many times
tests passed across multiple platforms. For instance, I have two columns; A
and B. Column A contains multiple platforms, ie: oracle, unix, mssql2K5.
Column B contains the results pass, fail, blocked.

ColA ColB
mssql2k5 pass
oracle pass
unix fail
mssql2k5 fail
oracle pass
unix fail
mssql2k5 blocked
oracle blocked
unix blocked


I can use COUNTIF and determine the number of pass/fail/blocked cases, and I
can determine the number of platforms tested on. What I can't seem to get is
the number of cases passed, etc. on mssql2K5, etc.

What I want is something like =COUNTIF(A:A,"mssql2k5") and
COUNTIF(B:B,"pass"). I tried using =COUNTIF(AND(AA:,"mssql2k5"),B:B,"pass")
, and the other way around, but that didn't work. Is this type of formula
possible?

Thank you both, however neither formula worked for me. I appreciate your
responses though. I can do what I need to do manually.

I basically need a formula that will filter out the configuration, and of
that configuration, how many passed. It seems simple enough, but I'm having
trouble.

Thanks again for your responses though. I appreciate it!

"Bob Phillips" wrote:

=SUMPRODUCT(--(A1:A100="mssql2k5"),--(B1:B100="pass"))

Note that SUMPRODUCT doesn't work with complete columns, you have to specify
a range.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"pkl" wrote in message
...
I'm using Excel 2003 SP2. I need a formula that will tell me how many
times
tests passed across multiple platforms. For instance, I have two columns;
A
and B. Column A contains multiple platforms, ie: oracle, unix, mssql2K5.
Column B contains the results pass, fail, blocked.

ColA ColB
mssql2k5 pass
oracle pass
unix fail
mssql2k5 fail
oracle pass
unix fail
mssql2k5 blocked
oracle blocked
unix blocked


I can use COUNTIF and determine the number of pass/fail/blocked cases, and
I
can determine the number of platforms tested on. What I can't seem to get
is
the number of cases passed, etc. on mssql2K5, etc.

What I want is something like =COUNTIF(A:A,"mssql2k5") and
COUNTIF(B:B,"pass"). I tried using
=COUNTIF(AND(AA:,"mssql2k5"),B:B,"pass")
, and the other way around, but that didn't work. Is this type of formula
possible?