count rows that meet two criteria in two different columns?

[dsk3808]

I need to count the number of times a person, who's name is in column A, has
a specific value,"0", in column B, where the worksheet contains an
ever-increasing amount of records (rows). Is there an easy way to make this
happen? Any help is appreciated. Thanks

[JonR]

Look at Jon Peltier's page on dynamic charts

http://peltiertech.com/Excel/Charts/index.html#hdrDyno

What you need is the information on creating dynamic named ranges, which
will expand or contract according to the size of your data. It looks a
little daunting at first, but you'll quickly get used to it. Then use the
range names in your calculations.

"dsk3808" wrote:

> I need to count the number of times a person, who's name is in column A, has
> a specific value,"0", in column B, where the worksheet contains an
> ever-increasing amount of records (rows). Is there an easy way to make this
> happen? Any help is appreciated. Thanks

[Max]

Something along these lines should suffice:

In C1: =SUMPRODUCT((A1:A20="Name1")*(B1:B20=0))

Use the smallest range sufficient
to cover the max expected extent of data in cols A and B
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dsk3808" wrote:
> I need to count the number of times a person, who's name is in column A, has
> a specific value,"0", in column B, where the worksheet contains an
> ever-increasing amount of records (rows). Is there an easy way to make this
> happen? Any help is appreciated. Thanks

[dsk3808]

Thanks for the response guys, but I'm not sure that either one does the job
I'm looking to do.

Basically, I need to use the SUMIF function with two criteria. Here is an
example of my current SUMIF functions:

=SUMIF('Data Record'!C:C,"Name",'Data Record'!I:I)

Which means that for every row in column C that contains "NAME" add in the
corresponding cell from column I.

What I need to do is expand that by one criteria.

I need the formula to say: For every row that contains "NAME" in column C
and contains "0" in column J, add 1.

Basically, I need a count function based on two criteria.
1) column contains a specific name AND
2) column J contains the number "0"

Hopefully this helps?

"Max" wrote:

> Something along these lines should suffice:
>
> In C1: =SUMPRODUCT((A1:A20="Name1")*(B1:B20=0))
>
> Use the smallest range sufficient
> to cover the max expected extent of data in cols A and B
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "dsk3808" wrote:
> > I need to count the number of times a person, who's name is in column A, has
> > a specific value,"0", in column B, where the worksheet contains an
> > ever-increasing amount of records (rows). Is there an easy way to make this
> > happen? Any help is appreciated. Thanks

[Max]

> =SUMIF('Data Record'!C:C,"Name",'Data Record'!I:I)
> I need the formula to say: For every row that contains "NAME" in column C
> and contains "0" in column J, add 1.

> > In C1: =SUMPRODUCT((A1:A20="Name1")*(B1:B20=0))

The earlier SUMPRODUCT should have worked (when adapted to suit your
layout), but we can't use entire col refs in SUMPRODUCT, unlike SUMIF. I
should have mentioned this in the earlier response, sorry.

Try something like this:
=SUMPRODUCT(('Data Record'!C2:C1000="Name")*('Data Record'!I2:I1000=0))

Adapt the ranges to suit. The ranges involved should be identical in size.
As stated earlier, use the smallest range sufficient to cover the max
expected extent of data in cols C and I. This is to keep performance optimal
/ tolerable* (calc speed).
*albeit this would be accepted, don't use eg: 'Data Record'!C2:C65536
when data is expected only up to, say row 2000
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"dsk3808" wrote:
> Thanks for the response guys, but I'm not sure that either one does the job
> I'm looking to do.
>
> Basically, I need to use the SUMIF function with two criteria. Here is an
> example of my current SUMIF functions:
>
> =SUMIF('Data Record'!C:C,"Name",'Data Record'!I:I)
>
> Which means that for every row in column C that contains "NAME" add in the
> corresponding cell from column I.
>
> What I need to do is expand that by one criteria.
>
> I need the formula to say: For every row that contains "NAME" in column C
> and contains "0" in column J, add 1.
>
> Basically, I need a count function based on two criteria.
> 1) column contains a specific name AND
> 2) column J contains the number "0"

[JonR]

Set up tow named ranges, one for column C (called "Name" or something you can
remember) and one for column I ("Value") outlined on Peltier's page. Then
enter this formula

=SUM(IF(Name="Bob",Value,0))

hold down the Ctrl and Shift keys when you press Enter. Your formula be
enclosed in brackets in the editing window

{=SUM(IF(Name="Bob",Value,0))}

In this case, every time the name in the range "Name" = "Bob" (Column C) it
will add the corresponding value in column I

HTH
JonR

"dsk3808" wrote:

> Thanks for the response guys, but I'm not sure that either one does the job
> I'm looking to do.
>
> Basically, I need to use the SUMIF function with two criteria. Here is an
> example of my current SUMIF functions:
>
> =SUMIF('Data Record'!C:C,"Name",'Data Record'!I:I)
>
> Which means that for every row in column C that contains "NAME" add in the
> corresponding cell from column I.
>
> What I need to do is expand that by one criteria.
>
> I need the formula to say: For every row that contains "NAME" in column C
> and contains "0" in column J, add 1.
>
> Basically, I need a count function based on two criteria.
> 1) column contains a specific name AND
> 2) column J contains the number "0"
>
> Hopefully this helps?
>
> "Max" wrote:
>
> > Something along these lines should suffice:
> >
> > In C1: =SUMPRODUCT((A1:A20="Name1")*(B1:B20=0))
> >
> > Use the smallest range sufficient
> > to cover the max expected extent of data in cols A and B
> > --
> > Max
> > Singapore
> > http://savefile.com/projects/236895
> > xdemechanik
> > ---
> > "dsk3808" wrote:
> > > I need to count the number of times a person, who's name is in column A, has
> > > a specific value,"0", in column B, where the worksheet contains an
> > > ever-increasing amount of records (rows). Is there an easy way to make this
> > > happen? Any help is appreciated. Thanks

[JonR]

Sorry, should have read the whole post.

Set up another name for Column J (call it "qualifier")

You can nest your IF statements thus:

=SUM(IF(Name="Bob",IF(Qualifier = 0,1,0),0)

Here I substituted "1" for the "Value" in the conditional sum statement,
since you are basically only adding 1 for each time all criteria are met, but
the concept is the same. Remember to hold down Ctrl-Shift when you press
Enter

"JonR" wrote:

> Set up tow named ranges, one for column C (called "Name" or something you can
> remember) and one for column I ("Value") outlined on Peltier's page. Then
> enter this formula
>
> =SUM(IF(Name="Bob",Value,0))
>
> hold down the Ctrl and Shift keys when you press Enter. Your formula be
> enclosed in brackets in the editing window
>
> {=SUM(IF(Name="Bob",Value,0))}
>
> In this case, every time the name in the range "Name" = "Bob" (Column C) it
> will add the corresponding value in column I
>
> HTH
> JonR
>
> "dsk3808" wrote:
>
> > Thanks for the response guys, but I'm not sure that either one does the job
> > I'm looking to do.
> >
> > Basically, I need to use the SUMIF function with two criteria. Here is an
> > example of my current SUMIF functions:
> >
> > =SUMIF('Data Record'!C:C,"Name",'Data Record'!I:I)
> >
> > Which means that for every row in column C that contains "NAME" add in the
> > corresponding cell from column I.
> >
> > What I need to do is expand that by one criteria.
> >
> > I need the formula to say: For every row that contains "NAME" in column C
> > and contains "0" in column J, add 1.
> >
> > Basically, I need a count function based on two criteria.
> > 1) column contains a specific name AND
> > 2) column J contains the number "0"
> >
> > Hopefully this helps?
> >
> > "Max" wrote:
> >
> > > Something along these lines should suffice:
> > >
> > > In C1: =SUMPRODUCT((A1:A20="Name1")*(B1:B20=0))
> > >
> > > Use the smallest range sufficient
> > > to cover the max expected extent of data in cols A and B
> > > --
> > > Max
> > > Singapore
> > > http://savefile.com/projects/236895
> > > xdemechanik
> > > ---
> > > "dsk3808" wrote:
> > > > I need to count the number of times a person, who's name is in column A, has
> > > > a specific value,"0", in column B, where the worksheet contains an
> > > > ever-increasing amount of records (rows). Is there an easy way to make this
> > > > happen? Any help is appreciated. Thanks