IF and lookup functions combined?

[RSS]

I need to know if anyone knows of an Excel function which can accomplish the
following task:

lookup a cell value within a given range (i.e., F33:F151) and if that value
meets

a certain criteria (i.e., >= 85% and/or <=94%), then execute a sumif
function

(i.e., sumif($h:h,A$2,$f$33:$f$151)). If the value doesn't match the
criteria, have Excel

return a value of 0.

Thanks,

ryan

 

[Guest]

Ryan

Try using sum product in an if statement with this

SUMPRODUCT((($F$33:$F$151*0.85)<=$A$1)*(($F$33:$F$151*0.95)>$A$1))>0

There appears to problem with your sumif. The areas should be the same:

sumif($h:h,A$2,$f$33:$f$151)
either
(1) sumif($h:$h,A$2,$f:$f)
or
(2) sumif($h$33:$h$151,A$2,$f$33:$f$151)

therefore lets assume (2)

=if(SUMPRODUCT((($F$33:$F$151*0.85)<=
$A$1)*(($F$33:$F$151*0.95)>$A$1))>0,
sumif($h$33:$h$151,A$2,$f$33:$f$151),0)

I'm not sure about your last sumif....but I leave that for you to consider.

 

[RSS]

My apologies. I just realized I had you chasing a ghost so to speak. The
range example should read "$J$33:$J$151" not "$f$33:$f$151". This changes
the question a bit to:

What function (or combination of functions) will lookup a cell value within
a given range (i.e., $J$33:$J$151) and if that value meets a certain
criteria (i.e., >= 85% and/or <=94%), then execute a sumif function (i.e.,
sumif($h$33:$h$151,$A$2,$f$33:$f$151)). If the value doesn't match, or fall
within the criteria have Excel return a value of 0.

Thank you.

 

[RSS]

I tried this formula it resulted in a value of 0 when the answer is not
zero. (I did the math the long way.)

Below are the columns I'm working with and the data they contain:

The data in Column F are annual salaries.
The data in Column H are ratings.
The data in Column J are compensation ratios.

I want to sum annual salaries where the associated comp ratios AND ratings
meet specific criteria. For example, I want to know the total annual salary
for all employees with a comp ratio between 85 & 94% AND a rating of 1.

Thanks in advance to anyone who might have an answer.