counting cells (COUNTIF) based on two or more criteria

Iam using Excel 2003 and I am trying to count a row based on two criteria.
For example: I am trying to count all those rows where cell B has a value of
"1" AND cell F has a value of "Completed".
The COUNTIF() function appears to only accept one group of parameters.
Using the COUNTIF() function twice in the cell with the + operator between
them appears to add those that qualify for each criteria and the "&" appears
to be giving me results I have yet to figure out.

I am trying to build a summary table like below based on a worksheet with
many detail records (rows) in another worksheet in the same workbook:
Status Level 1 Level 2 Level 3 .... Level 10
Open 10 13 1 24
Started 9 7 3 18
Completed 5 9 12 11
Etc

Try this...

=sumproduct(--(B2:B10=1),--(F2:F10="Completed"))

You can continue adding as many extra conditions as you like.
Hope this helps!


"Tricia S." wrote:

> Iam using Excel 2003 and I am trying to count a row based on two criteria.
> For example: I am trying to count all those rows where cell B has a value of
> "1" AND cell F has a value of "Completed".
> The COUNTIF() function appears to only accept one group of parameters.
> Using the COUNTIF() function twice in the cell with the + operator between
> them appears to add those that qualify for each criteria and the "&" appears
> to be giving me results I have yet to figure out.
>
> I am trying to build a summary table like below based on a worksheet with
> many detail records (rows) in another worksheet in the same workbook:
> Status Level 1 Level 2 Level 3 .... Level 10
> Open 10 13 1 24
> Started 9 7 3 18
> Completed 5 9 12 11
> Etc
>

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tricia S." <Tricia [email protected]> wrote in message
news:[email protected]...
> Iam using Excel 2003 and I am trying to count a row based on two criteria.
> For example: I am trying to count all those rows where cell B has a value
of
> "1" AND cell F has a value of "Completed".
> The COUNTIF() function appears to only accept one group of parameters.
> Using the COUNTIF() function twice in the cell with the + operator between
> them appears to add those that qualify for each criteria and the "&"
appears
> to be giving me results I have yet to figure out.
>
> I am trying to build a summary table like below based on a worksheet with
> many detail records (rows) in another worksheet in the same workbook:
> Status Level 1 Level 2 Level 3 .... Level 10
> Open 10 13 1 24
> Started 9 7 3 18
> Completed 5 9 12 11
> Etc
>

=SUMPRODUCT(--(B1:B100=1),--(F1:F100="Completed"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tricia S." <Tricia [email protected]> wrote in message
news:[email protected]...
> Iam using Excel 2003 and I am trying to count a row based on two criteria.
> For example: I am trying to count all those rows where cell B has a value
of
> "1" AND cell F has a value of "Completed".
> The COUNTIF() function appears to only accept one group of parameters.
> Using the COUNTIF() function twice in the cell with the + operator between
> them appears to add those that qualify for each criteria and the "&"
appears
> to be giving me results I have yet to figure out.
>
> I am trying to build a summary table like below based on a worksheet with
> many detail records (rows) in another worksheet in the same workbook:
> Status Level 1 Level 2 Level 3 .... Level 10
> Open 10 13 1 24
> Started 9 7 3 18
> Completed 5 9 12 11
> Etc
>

Careful...per XL Help, SUMPRODUCT is limited to 30 arrays.

Jason

>-----Original Message-----
>Try this...
>
>=sumproduct(--(B2:B10=1),--(F2:F10="Completed"))
>
>You can continue adding as many extra conditions as you
like.
>Hope this helps!
>
>
>"Tricia S." wrote:
>
>> Iam using Excel 2003 and I am trying to count a row
based on two criteria.
>> For example: I am trying to count all those rows
where cell B has a value of
>> "1" AND cell F has a value of "Completed".
>> The COUNTIF() function appears to only accept one
group of parameters.
>> Using the COUNTIF() function twice in the cell with
the + operator between
>> them appears to add those that qualify for each
criteria and the "&" appears
>> to be giving me results I have yet to figure out.
>>
>> I am trying to build a summary table like below based
on a worksheet with
>> many detail records (rows) in another worksheet in the
same workbook:
>> Status Level 1 Level 2 Level 3 .... Level
10
>> Open 10 13
1 24
>> Started 9 7
3 18
>> Completed 5 9
12 11
>> Etc
>>
>.
>

Frank Kabel's reply to another post (use sUMPRODUCT. See:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
) helped. However, in order for it to work for me, I had to make sure the
first column I was evaluating did not contain numeric data. Example:

Column A quality fruit color
AA 1 apple red
AA 1 orange green
BB 1 apple yellow
BB 2 pear green

I needed to know how many Quality 1 red apples there were. The quality
column test cannot be first in the SUM(IF(....) functions. The parameters
needed to fall in the order of Color, fruit, quality OR fruit, color,
quality, but quality could not be the first test performed (with or without
quotes around the "1").

If someone knows a way around that, I'd love to entertain the suggestions.
But for now, I have my answer. Thank you, Tricia

"Tricia S." wrote:

> I am using Excel 2003 and I am trying to count a row based on two criteria.
> For example: I am trying to count all those rows where cell B has a value of
> "1" AND cell F has a value of "Completed".
> The COUNTIF() function appears to only accept one group of parameters.
> Using the COUNTIF() function twice in the cell with the + operator between
> them appears to add those that qualify for each criteria and the "&" appears
> to be giving me results I have yet to figure out.
>
> I am trying to build a summary table like below based on a worksheet with
> many detail records (rows) in another worksheet in the same workbook:
> Status Level 1 Level 2 Level 3 .... Level 10
> Open 10 13 1 24
> Started 9 7 3 18
> Completed 5 9 12 11
> Etc
>

Thank you. A reply to another post lead me down the SUM(IF()) nested
functions which is working, but the SUMPRODUCT is much easier to read and
maintain than nested SUM(IF()). I'd like to change over to the SUMPRODUCT
function, however, I am unable to count where column B is empty (a value has
not yet been assigned to that row/column), but the other columns for that row
have values. I want to make sure not to count the blank rows at the bottom
of the spreadsheet.

"Bob Phillips" wrote:

> =SUMPRODUCT(--(B1:B100=1),--(F1:F100="Completed"))
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Tricia S." <Tricia [email protected]> wrote in message
> news:[email protected]...
> > Iam using Excel 2003 and I am trying to count a row based on two criteria.
> > For example: I am trying to count all those rows where cell B has a value
> of
> > "1" AND cell F has a value of "Completed".
> > The COUNTIF() function appears to only accept one group of parameters.
> > Using the COUNTIF() function twice in the cell with the + operator between
> > them appears to add those that qualify for each criteria and the "&"
> appears
> > to be giving me results I have yet to figure out.
> >
> > I am trying to build a summary table like below based on a worksheet with
> > many detail records (rows) in another worksheet in the same workbook:
> > Status Level 1 Level 2 Level 3 .... Level 10
> > Open 10 13 1 24
> > Started 9 7 3 18
> > Completed 5 9 12 11
> > Etc
> >
>
>
>

Tricia,

Surely if B is empty it fails the B1:B100=1 test. If you do want to ignore
blanks, just add that as a condition

--(B1:B100<>"")

Glad you like the SUMPRODUCT web page :-)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tricia S." <[email protected]> wrote in message
news:[email protected]...
> Thank you. A reply to another post lead me down the SUM(IF()) nested
> functions which is working, but the SUMPRODUCT is much easier to read and
> maintain than nested SUM(IF()). I'd like to change over to the SUMPRODUCT
> function, however, I am unable to count where column B is empty (a value
has
> not yet been assigned to that row/column), but the other columns for that
row
> have values. I want to make sure not to count the blank rows at the
bottom
> of the spreadsheet.
>
> "Bob Phillips" wrote:
>
> > =SUMPRODUCT(--(B1:B100=1),--(F1:F100="Completed"))
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Tricia S." <Tricia [email protected]> wrote in message
> > news:[email protected]...
> > > Iam using Excel 2003 and I am trying to count a row based on two
criteria.
> > > For example: I am trying to count all those rows where cell B has a
value
> > of
> > > "1" AND cell F has a value of "Completed".
> > > The COUNTIF() function appears to only accept one group of parameters.
> > > Using the COUNTIF() function twice in the cell with the + operator
between
> > > them appears to add those that qualify for each criteria and the "&"
> > appears
> > > to be giving me results I have yet to figure out.
> > >
> > > I am trying to build a summary table like below based on a worksheet
with
> > > many detail records (rows) in another worksheet in the same workbook:
> > > Status Level 1 Level 2 Level 3 .... Level 10
> > > Open 10 13 1 24
> > > Started 9 7 3 18
> > > Completed 5 9 12 11
> > > Etc
> > >
> >
> >
> >

It does fail, but I need to capture it in a count of all those that have
nothing in column B, but have a value in column F, among other criteria
(which I'm not having problems with <yet>). The two below do not work (or
I'm doing something wrong)

I should get a result of 1 (but it's returning 0)

=SUMPRODUCT(--($B$1:$B$100=""),--($F$1:$F$100="Completed"),--($A$1:$A$100="AA"))

=SUMPRODUCT(--($B$1:$B$100="
"),--($F$1:$F$100="Completed"),--($A$1:$A$100="AA"))

"Bob Phillips" wrote:

> Tricia,
>
> Surely if B is empty it fails the B1:B100=1 test. If you do want to ignore
> blanks, just add that as a condition
>
> --(B1:B100<>"")
>
> Glad you like the SUMPRODUCT web page :-)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Tricia S." <[email protected]> wrote in message
> news:[email protected]...
> > Thank you. A reply to another post lead me down the SUM(IF()) nested
> > functions which is working, but the SUMPRODUCT is much easier to read and
> > maintain than nested SUM(IF()). I'd like to change over to the SUMPRODUCT
> > function, however, I am unable to count where column B is empty (a value
> has
> > not yet been assigned to that row/column), but the other columns for that
> row
> > have values. I want to make sure not to count the blank rows at the
> bottom
> > of the spreadsheet.
> >
> > "Bob Phillips" wrote:
> >
> > > =SUMPRODUCT(--(B1:B100=1),--(F1:F100="Completed"))
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Tricia S." <Tricia [email protected]> wrote in message
> > > news:[email protected]...
> > > > Iam using Excel 2003 and I am trying to count a row based on two
> criteria.
> > > > For example: I am trying to count all those rows where cell B has a
> value
> > > of
> > > > "1" AND cell F has a value of "Completed".
> > > > The COUNTIF() function appears to only accept one group of parameters.
> > > > Using the COUNTIF() function twice in the cell with the + operator
> between
> > > > them appears to add those that qualify for each criteria and the "&"
> > > appears
> > > > to be giving me results I have yet to figure out.
> > > >
> > > > I am trying to build a summary table like below based on a worksheet
> with
> > > > many detail records (rows) in another worksheet in the same workbook:
> > > > Status Level 1 Level 2 Level 3 .... Level 10
> > > > Open 10 13 1 24
> > > > Started 9 7 3 18
> > > > Completed 5 9 12 11
> > > > Etc
> > > >
> > >
> > >
> > >
>
>
>

Tricia,

There is nothing wrong with the formula, I entered AA in A1, Completed in
F1, and I got a 1.

Does your AA or Completed have any trailing spaces?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tricia S." <[email protected]> wrote in message
news:[email protected]...
> It does fail, but I need to capture it in a count of all those that have
> nothing in column B, but have a value in column F, among other criteria
> (which I'm not having problems with <yet>). The two below do not work (or
> I'm doing something wrong)
>
> I should get a result of 1 (but it's returning 0)
>
>
=SUMPRODUCT(--($B$1:$B$100=""),--($F$1:$F$100="Completed"),--($A$1:$A$100="A
A"))
>
> =SUMPRODUCT(--($B$1:$B$100="
> "),--($F$1:$F$100="Completed"),--($A$1:$A$100="AA"))
>
> "Bob Phillips" wrote:
>
> > Tricia,
> >
> > Surely if B is empty it fails the B1:B100=1 test. If you do want to
ignore
> > blanks, just add that as a condition
> >
> > --(B1:B100<>"")
> >
> > Glad you like the SUMPRODUCT web page :-)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Tricia S." <[email protected]> wrote in message
> > news:[email protected]...
> > > Thank you. A reply to another post lead me down the SUM(IF()) nested
> > > functions which is working, but the SUMPRODUCT is much easier to read
and
> > > maintain than nested SUM(IF()). I'd like to change over to the
SUMPRODUCT
> > > function, however, I am unable to count where column B is empty (a
value
> > has
> > > not yet been assigned to that row/column), but the other columns for
that
> > row
> > > have values. I want to make sure not to count the blank rows at the
> > bottom
> > > of the spreadsheet.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > =SUMPRODUCT(--(B1:B100=1),--(F1:F100="Completed"))
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "Tricia S." <Tricia [email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Iam using Excel 2003 and I am trying to count a row based on two
> > criteria.
> > > > > For example: I am trying to count all those rows where cell B has
a
> > value
> > > > of
> > > > > "1" AND cell F has a value of "Completed".
> > > > > The COUNTIF() function appears to only accept one group of
parameters.
> > > > > Using the COUNTIF() function twice in the cell with the + operator
> > between
> > > > > them appears to add those that qualify for each criteria and the
"&"
> > > > appears
> > > > > to be giving me results I have yet to figure out.
> > > > >
> > > > > I am trying to build a summary table like below based on a
worksheet
> > with
> > > > > many detail records (rows) in another worksheet in the same
workbook:
> > > > > Status Level 1 Level 2 Level 3 .... Level 10
> > > > > Open 10 13 1 24
> > > > > Started 9 7 3 18
> > > > > Completed 5 9 12 11
> > > > > Etc
> > > > >
> > > >
> > > >
> > > >
> >
> >
> >

No, no trailing spaces. And now that I've entered the formula into a new
spreadsheet it's working as expected. Odd. There must have been something
strange with the data in the previous spreadsheet.

It's all working now, and I very much appreciate your help! Have a nice day.

Tricia

"Bob Phillips" wrote:

> Tricia,
>
> There is nothing wrong with the formula, I entered AA in A1, Completed in
> F1, and I got a 1.
>
> Does your AA or Completed have any trailing spaces?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Tricia S." <[email protected]> wrote in message
> news:[email protected]...
> > It does fail, but I need to capture it in a count of all those that have
> > nothing in column B, but have a value in column F, among other criteria
> > (which I'm not having problems with <yet>). The two below do not work (or
> > I'm doing something wrong)
> >
> > I should get a result of 1 (but it's returning 0)
> >
> >
> =SUMPRODUCT(--($B$1:$B$100=""),--($F$1:$F$100="Completed"),--($A$1:$A$100="A
> A"))
> >
> > =SUMPRODUCT(--($B$1:$B$100="
> > "),--($F$1:$F$100="Completed"),--($A$1:$A$100="AA"))
> >
> > "Bob Phillips" wrote:
> >
> > > Tricia,
> > >
> > > Surely if B is empty it fails the B1:B100=1 test. If you do want to
> ignore
> > > blanks, just add that as a condition
> > >
> > > --(B1:B100<>"")
> > >
> > > Glad you like the SUMPRODUCT web page :-)
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "Tricia S." <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Thank you. A reply to another post lead me down the SUM(IF()) nested
> > > > functions which is working, but the SUMPRODUCT is much easier to read
> and
> > > > maintain than nested SUM(IF()). I'd like to change over to the
> SUMPRODUCT
> > > > function, however, I am unable to count where column B is empty (a
> value
> > > has
> > > > not yet been assigned to that row/column), but the other columns for
> that
> > > row
> > > > have values. I want to make sure not to count the blank rows at the
> > > bottom
> > > > of the spreadsheet.
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > =SUMPRODUCT(--(B1:B100=1),--(F1:F100="Completed"))
> > > > >
> > > > > --
> > > > >
> > > > > HTH
> > > > >
> > > > > RP
> > > > > (remove nothere from the email address if mailing direct)
> > > > >
> > > > >
> > > > > "Tricia S." <Tricia [email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > Iam using Excel 2003 and I am trying to count a row based on two
> > > criteria.
> > > > > > For example: I am trying to count all those rows where cell B has
> a
> > > value
> > > > > of
> > > > > > "1" AND cell F has a value of "Completed".
> > > > > > The COUNTIF() function appears to only accept one group of
> parameters.
> > > > > > Using the COUNTIF() function twice in the cell with the + operator
> > > between
> > > > > > them appears to add those that qualify for each criteria and the
> "&"
> > > > > appears
> > > > > > to be giving me results I have yet to figure out.
> > > > > >
> > > > > > I am trying to build a summary table like below based on a
> worksheet
> > > with
> > > > > > many detail records (rows) in another worksheet in the same
> workbook:
> > > > > > Status Level 1 Level 2 Level 3 .... Level 10
> > > > > > Open 10 13 1 24
> > > > > > Started 9 7 3 18
> > > > > > Completed 5 9 12 11
> > > > > > Etc
> > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>