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

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.

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

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.

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

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

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

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

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

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


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=563354

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

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

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

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