| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| IF and lookup functions combined? | Excel Worksheet Functions | |||
| Combining LOOKUP and COUNTIF functions | Excel Worksheet Functions | |||
| INFORMATION ON FUNCTIONS IN EXCEL (EX. "IF", VALUE LOOKUP) | Excel Worksheet Functions | |||
| Allow refence in the "table_array" position of Lookup functions | Excel Worksheet Functions | |||
| Excel Lookup Functions | Excel Worksheet Functions | |||
Linear Mode
