IF and lookup functions combined?
 

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

IF and lookup functions combined?
 

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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"RSS" wrote:

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

IF and lookup functions combined?
 

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.


"Martin Fishlock" wrote in message
...
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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"RSS" wrote:

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

IF and lookup functions combined?
 

It is quite easy to change:

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

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"RSS" wrote:

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.


"Martin Fishlock" wrote in message
...
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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"RSS" wrote:

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

IF and lookup functions combined?
 

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.


"Martin Fishlock" wrote in message
...
It is quite easy to change:

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

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"RSS" wrote:

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.


"Martin Fishlock" wrote in message
...
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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"RSS" wrote:

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