Count cells that contain "Y" in columnA IF contains"X" in columnB

[holliedavis]

Excel 2003- I'm trying to compose a function that will return a numeric value
of cells that contain a given value in one column, but only if they also
contain a given value in another coulumn. For example, I want to know how
many candidates for a job have accepted their offer (contain "Y" in "Offer
Accepted?" column) for each manager's area (contain "John Smith" in "Region
of Placement" column). The spreadsheet which contains the data I want to
count lists all candidates that have been offered positions, for all regions,
sorted by Candidate's last name. I want the Summary worksheet (which will
contain the formula) to tell me how many accepted for each individual region.

[holliedavis]

For example:

Manager Offer
Candidate Region Accepted?
Doe, Jane J White Y
Smith, Jon B Davis Y
Johnson, Jack J White N

I want to count the # of "Y" in column "Offer Accepted", IF column "Manager
Region" contains "J White".

"holliedavis" wrote:

> Excel 2003- I'm trying to compose a function that will return a numeric value
> of cells that contain a given value in one column, but only if they also
> contain a given value in another coulumn. For example, I want to know how
> many candidates for a job have accepted their offer (contain "Y" in "Offer
> Accepted?" column) for each manager's area (contain "John Smith" in "Region
> of Placement" column). The spreadsheet which contains the data I want to
> count lists all candidates that have been offered positions, for all regions,
> sorted by Candidate's last name. I want the Summary worksheet (which will
> contain the formula) to tell me how many accepted for each individual region.

[SteveG]

If Offer Accepted is column A and region column B then,

=SUMPRODUCT((A1:A100="Y")*(B1:B100="John Smith"))

Note: SUMPRODUCT requires that the ranges be the same size.


HTH

Steve

[holliedavis]

I need this formula on sheet "Summary". The info is being pulled from sheet
"Offers" within the same workbook. The "Offers Accepted is column Y, and the
Region is column H.

I entered the formulas as follows:
=SUMPRODUCT((Offers!H:H="J White")*(Offers!Y:Y="Y"))

I received a #NUM! error... Help?


"SteveG" wrote:

>
> If Offer Accepted is column A and region column B then,
>
> =SUMPRODUCT((A1:A100="Y")*(B1:B100="John Smith"))
>
> Note: SUMPRODUCT requires that the ranges be the same size.
>
>
> HTH
>
> Steve
>
>
> --
> SteveG
> ------------------------------------------------------------------------
> SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> View this thread: http://www.excelforum.com/showthread...hreadid=563354
>
>

[SteveG]

Hollie,

SUMPRODUCT does not allow you to use an entire column or row (A:A) as a range. Just change to something like,

=SUMPRODUCT((Offers!H1:H65000="J White")*(Offers!Y1:Y65000="Y"))

Should have warned you of that.

HTH

Steve

[Scoops]

holliedavis wrote:
> I need this formula on sheet "Summary". The info is being pulled from sheet
> "Offers" within the same workbook. The "Offers Accepted is column Y, and the
> Region is column H.
>
> I entered the formulas as follows:
> =SUMPRODUCT((Offers!H:H="J White")*(Offers!Y:Y="Y"))
>
> I received a #NUM! error... Help?

Hi holliedavis

SUMPRODUCT doesn't work with whole column references; specify the range
you want.

Regards

Steve

[Toppers]

SUMPRODUCT has to have arrays defined e.g H1:H2000 as it cannot use total
columns as per your formula.

=SUMPRODUCT(--(Offers!H1:H5000="J White"),--(Offers!Y1:Y5000="Y"))

-- converts TRUE/FALSE to numeric 1/0


HTH

"holliedavis" wrote:

> I need this formula on sheet "Summary". The info is being pulled from sheet
> "Offers" within the same workbook. The "Offers Accepted is column Y, and the
> Region is column H.


>
> I entered the formulas as follows:
> =SUMPRODUCT((Offers!H:H="J White")*(Offers!Y:Y="Y"))
>
> I received a #NUM! error... Help?
>
>
> "SteveG" wrote:
>
> >
> > If Offer Accepted is column A and region column B then,
> >
> > =SUMPRODUCT((A1:A100="Y")*(B1:B100="John Smith"))
> >
> > Note: SUMPRODUCT requires that the ranges be the same size.
> >
> >
> > HTH
> >
> > Steve
> >
> >
> > --
> > SteveG
> > ------------------------------------------------------------------------
> > SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
> > View this thread: http://www.excelforum.com/showthread...hreadid=563354
> >
> >