Dear Garys Student,
Sorry for the delay in getting back to you, but this is the first I chance I
had to see the new replies to my post. I tried your suggestion and came up
with the following change to my equation:
=SUMPRODUCT(--('[Rates.xls]Sheet1'!$S$3:$S$261=$E91),--('[Rates.xls]Sheet1'!$K$3:$K$261=$F91))
However, this simply counts all occurrences of all the companies in the
Rates table. I tried Maxs solution below and that correctly displays the
number of matching results found for the multiple criteria which are being
evaluated. I took a look at the link that you provided, and while I havent
finished reading the whole page yet, its helping me understand the
SUMPRODUCT function. One thing I learned was that by using the SUMPRODUCT
function, I dont need to keep my Rates workbook open to find the results.
Thanks for your help and look forward to reading the rest of the link that
you gave me.
"Gary''s Student" wrote:
You may need to enclose the 1 within the --
In any case, debug the SUMPRODUCT before using it for the formatting. Help
is not very helpful, instead see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
Gary's Student
gsnu200709
"RS" wrote:
Hi Gary's Student,
Since I've never used SUMPRODUCT before, I looked at the Excel Help (no
help there) and the forums here to try and fix my equation. What I came up
with is the following:
=SUMPRODUCT(--('[Rates.xls]Sheet1'!$S$3:$S$261=$E8)1,--('[Rates.xls]Sheet1'!$K$3:$K$261=$F8)1)
However, I'm doing something wrong, because it's returning 0 for something
that, with the other formula, gave a result of TRUE because there was 1
possible value for the 2 matching criteria. What am I doing wrong?
"Gary''s Student" wrote:
use SUMPRODUCT()
--
Gary''s Student
gsnu200709
"RS" wrote:
In Excel 2000, Im told that I cant reference other worksheets or workbooks
when using conditional formatting. Looking through these forums, I put
together a formula that highlights cells if there are duplicate listings of
companies found in column S (row 2 has headers and the companies are listed
in cells S3:S261. The conditional format in this €śRates.xls€ť file for S3,
for example, is:
Formula Is =COUNTIF(S:S,S3)1.
I want to use a similar conditional format to highlight cells in column G of
my other workbook (Test Rate) whereby I find pay rates from the €śRates€ť file
based on TWO criteria: column E (company name) that matches values in column
S of the €śRates€ť file; and column F, which concatenates columns B (Type), C
(Program), & D (Model) with /s in the €śTest Rate€ť file to match the values
found in column K of the €śRates€ť file. Since Im told that I cant reference
other worksheets or workbooks in the conditional format, Ive created a
formula in column L which basically checks to see if BOTH conditions are met
and returns either True or False. The formula in cell L8 is:
=AND(COUNTIF('[Rates.xls]Sheet1'!$S:$S,$E8)1,COUNTIF('[Rates.xls]Sheet1'!$K:$K,$F8)1)
My problem is that this formula is evaluating each condition independently
and I need it to evaluate BOTH conditions TOGETHER. Currently this formula
is incorrectly returning TRUE for an entry that should be FALSE because
although there is 1 company listing in the €śRates€ť file (this evaluates as
TRUE), AND there is 1 listing of the value in F8 (Networks/Res/Home; which
also evaluates as TRUE), the combination of both items together results in
only 1 value for each combination.
In other words, even though there are 2 listings for €śCompany A€ť, the values
in column F are different for each of the 2 listings. Column F of one of
Company As listings is Networks/Res/School (of which there are 58 listings)
and in the other of Company As listings, column F is Networks/Res/Home (of
which there are 70 listings). Because the formula is currently evaluating
each condition independently, the 2nd half of the formula is returning TRUE
because there are multiple listings for this half, but evaluating BOTH
conditions TOGETHER, the formula should return FALSE. How do I correct my
formula?