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.

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.

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

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

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

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

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