|
Help
Hi, i am trying to compare 1 cell with a range of cells to give an answer in
P81. =IF(K81=5,SUMPRODUCT(--($B$5:$B$10=I92)),"") This works fine for once cell (K81) but i need to it to work for K81:O81=5 but if non of them =5 i want it blank. When i put =IF(K81:O81=5,SUMPRODUCT(--($B$5:$B$10=I92)),"") i get #VALUE. TIA |
Try...
=IF(ISNUMBER(MATCH(K81:O81,{5},0)),SUMPRODUCT(--($B$5:$B$10=I92)),"") Actually, the following formula would suffice... =IF(ISNUMBER(MATCH(K81:O81,{5},0)),COUNTIF($B$5:$B $10,I92),"") No need to use SUMPRODUCT to count with one condition. Hope this helps! In article , "Phil" wrote: Hi, i am trying to compare 1 cell with a range of cells to give an answer in P81. =IF(K81=5,SUMPRODUCT(--($B$5:$B$10=I92)),"") This works fine for once cell (K81) but i need to it to work for K81:O81=5 but if non of them =5 i want it blank. When i put =IF(K81:O81=5,SUMPRODUCT(--($B$5:$B$10=I92)),"") i get #VALUE. TIA |
Thx but neither work as i want. With them i get blank value but if i delete
O81 from both formulae i get a result. "Domenic" wrote in message ... Try... =IF(ISNUMBER(MATCH(K81:O81,{5},0)),SUMPRODUCT(--($B$5:$B$10=I92)),"") Actually, the following formula would suffice... =IF(ISNUMBER(MATCH(K81:O81,{5},0)),COUNTIF($B$5:$B $10,I92),"") No need to use SUMPRODUCT to count with one condition. Hope this helps! In article , "Phil" wrote: Hi, i am trying to compare 1 cell with a range of cells to give an answer in P81. =IF(K81=5,SUMPRODUCT(--($B$5:$B$10=I92)),"") This works fine for once cell (K81) but i need to it to work for K81:O81=5 but if non of them =5 i want it blank. When i put =IF(K81:O81=5,SUMPRODUCT(--($B$5:$B$10=I92)),"") i get #VALUE. TIA |
Correction...
=IF(ISNUMBER(MATCH(5,K81:O81,0)),SUMPRODUCT(--($B$5:$B$10=I92)),"") OR =IF(ISNUMBER(MATCH(5,K81:O81,0)),COUNTIF($B$5:$B$1 0,I92),"") Hope this helps! In article , Domenic wrote: Try... =IF(ISNUMBER(MATCH(K81:O81,{5},0)),SUMPRODUCT(--($B$5:$B$10=I92)),"") Actually, the following formula would suffice... =IF(ISNUMBER(MATCH(K81:O81,{5},0)),COUNTIF($B$5:$B $10,I92),"") No need to use SUMPRODUCT to count with one condition. Hope this helps! |
That works, thanks.
Is it possible to seperate the columns K-O as each refers to different list of numbers? i.e the number in K looks at the cells B5:B10, L looks at C5:C10 up to O - F5:F10 I have tried this.. =IF(ISNUMBER(MATCH(5,K81:O81,0)),SUMPRODUCT(--($B$5:$F$10=I92)),"") but if 5 appears in say K its looks at the whole range B5:F10 when all i want it to do is look at B5:B10, similarly if its in L it looks through the whole range when i want it to look at C5:C10.. What i am trying to say is i want your formula, =IF(ISNUMBER(MATCH(5,K81:O81,0)),SUMPRODUCT(--($B$5:$F$10=I92)),""), to somehow be 5 formulae in 1 cell. Tricky ? Too much work? or impossible? Thanks again "Domenic" wrote in message ... Correction... =IF(ISNUMBER(MATCH(5,K81:O81,0)),SUMPRODUCT(--($B$5:$B$10=I92)),"") OR =IF(ISNUMBER(MATCH(5,K81:O81,0)),COUNTIF($B$5:$B$1 0,I92),"") Hope this helps! In article , Domenic wrote: Try... =IF(ISNUMBER(MATCH(K81:O81,{5},0)),SUMPRODUCT(--($B$5:$B$10=I92)),"") Actually, the following formula would suffice... =IF(ISNUMBER(MATCH(K81:O81,{5},0)),COUNTIF($B$5:$B $10,I92),"") No need to use SUMPRODUCT to count with one condition. Hope this helps! |
=IF(ISNUMBER(MATCH(5,K81:O81,0)),COUNTIF(INDEX($B$ 5:$F$10,0,MATCH(5,K81:O81,0))),"")
Phil wrote: That works, thanks. Is it possible to seperate the columns K-O as each refers to different list of numbers? i.e the number in K looks at the cells B5:B10, L looks at C5:C10 up to O - F5:F10 I have tried this.. =IF(ISNUMBER(MATCH(5,K81:O81,0)),SUMPRODUCT(--($B$5:$F$10=I92)),"") but if 5 appears in say K its looks at the whole range B5:F10 when all i want it to do is look at B5:B10, similarly if its in L it looks through the whole range when i want it to look at C5:C10.. What i am trying to say is i want your formula, =IF(ISNUMBER(MATCH(5,K81:O81,0)),SUMPRODUCT(--($B$5:$F$10=I92)),""), to somehow be 5 formulae in 1 cell. Tricky ? Too much work? or impossible? Thanks again "Domenic" wrote in message ... Correction... =IF(ISNUMBER(MATCH(5,K81:O81,0)),SUMPRODUCT(--($B$5:$B$10=I92)),"") OR =IF(ISNUMBER(MATCH(5,K81:O81,0)),COUNTIF($B$5:$B $10,I92),"") Hope this helps! In article , Domenic wrote: Try... =IF(ISNUMBER(MATCH(K81:O81,{5},0)),SUMPRODUCT (--($B$5:$B$10=I92)),"") Actually, the following formula would suffice... =IF(ISNUMBER(MATCH(K81:O81,{5},0)),COUNTIF($B$5 :$B$10,I92),"") No need to use SUMPRODUCT to count with one condition. Hope this helps! |
I think Aladin meant...
=IF(ISNUMBER(MATCH(5,K81:O81,0)),COUNTIF(INDEX($B$ 5:$F$10,0,MATCH(5,K81:O 81,0)),I92),"") Hope this helps! In article , Aladin Akyurek wrote: =IF(ISNUMBER(MATCH(5,K81:O81,0)),COUNTIF(INDEX($B$ 5:$F$10,0,MATCH(5,K81:O81,0) )),"") Phil wrote: That works, thanks. Is it possible to seperate the columns K-O as each refers to different list of numbers? i.e the number in K looks at the cells B5:B10, L looks at C5:C10 up to O - F5:F10 I have tried this.. =IF(ISNUMBER(MATCH(5,K81:O81,0)),SUMPRODUCT(--($B$5:$F$10=I92)),"") but if 5 appears in say K its looks at the whole range B5:F10 when all i want it to do is look at B5:B10, similarly if its in L it looks through the whole range when i want it to look at C5:C10.. What i am trying to say is i want your formula, =IF(ISNUMBER(MATCH(5,K81:O81,0)),SUMPRODUCT(--($B$5:$F$10=I92)),""), to somehow be 5 formulae in 1 cell. Tricky ? Too much work? or impossible? Thanks again "Domenic" wrote in message ... Correction... =IF(ISNUMBER(MATCH(5,K81:O81,0)),SUMPRODUCT(--($B$5:$B$10=I92)),"") OR =IF(ISNUMBER(MATCH(5,K81:O81,0)),COUNTIF($B$5:$B $10,I92),"") Hope this helps! In article , Domenic wrote: Try... =IF(ISNUMBER(MATCH(K81:O81,{5},0)),SUMPRODUCT (--($B$5:$B$10=I92)),"") Actually, the following formula would suffice... =IF(ISNUMBER(MATCH(K81:O81,{5},0)),COUNTIF($B$5 :$B$10,I92),"") No need to use SUMPRODUCT to count with one condition. Hope this helps! |
Contains error.
i cant find it! "Domenic" wrote in message ... I think Aladin meant... =IF(ISNUMBER(MATCH(5,K81:O81,0)),COUNTIF(INDEX($B$ 5:$F$10,0,MATCH(5,K81:O 81,0)),I92),"") Hope this helps! In article , Aladin Akyurek wrote: =IF(ISNUMBER(MATCH(5,K81:O81,0)),COUNTIF(INDEX($B$ 5:$F$10,0,MATCH(5,K81:O81,0) )),"") Phil wrote: That works, thanks. Is it possible to seperate the columns K-O as each refers to different list of numbers? i.e the number in K looks at the cells B5:B10, L looks at C5:C10 up to O - F5:F10 I have tried this.. =IF(ISNUMBER(MATCH(5,K81:O81,0)),SUMPRODUCT(--($B$5:$F$10=I92)),"") but if 5 appears in say K its looks at the whole range B5:F10 when all i want it to do is look at B5:B10, similarly if its in L it looks through the whole range when i want it to look at C5:C10.. What i am trying to say is i want your formula, =IF(ISNUMBER(MATCH(5,K81:O81,0)),SUMPRODUCT(--($B$5:$F$10=I92)),""), to somehow be 5 formulae in 1 cell. Tricky ? Too much work? or impossible? Thanks again "Domenic" wrote in message ... Correction... =IF(ISNUMBER(MATCH(5,K81:O81,0)),SUMPRODUCT(--($B$5:$B$10=I92)),"") OR =IF(ISNUMBER(MATCH(5,K81:O81,0)),COUNTIF($B$5:$B $10,I92),"") Hope this helps! In article , Domenic wrote: Try... =IF(ISNUMBER(MATCH(K81:O81,{5},0)),SUMPRODUCT (--($B$5:$B$10=I92)),"") Actually, the following formula would suffice... =IF(ISNUMBER(MATCH(K81:O81,{5},0)),COUNTIF($B$5 :$B$10,I92),"") No need to use SUMPRODUCT to count with one condition. Hope this helps! |
In article ,
"Phil" wrote: Contains error. i cant find it! If you copied and pasted the formula into your spreadsheet, make sure you don't have a line return or additional spaces inserted in the formula. |
"Domenic" wrote...
I think Aladin meant... =IF(ISNUMBER(MATCH(5,K81:O81,0)),COUNTIF(INDEX($B $5:$F$10,0, MATCH(5,K81:O81,0)),I92),"") .... Is ISNUMBER(MATCH(5,K81:O81,0)) really more efficient than COUNTIF(K81:O81,5)? |
Harlan Grove wrote:
"Domenic" wrote... I think Aladin meant... =IF(ISNUMBER(MATCH(5,K81:O81,0)),COUNTIF(INDEX($ B$5:$F$10,0, MATCH(5,K81:O81,0)),I92),"") ... Is ISNUMBER(MATCH(5,K81:O81,0)) really more efficient than COUNTIF(K81:O81,5)? http://www.mrexcel.com/board2/viewtopic.php?t=40233 compares their temporal profile. |
"Aladin Akyurek" wrote...
Harlan Grove wrote: .... Is ISNUMBER(MATCH(5,K81:O81,0)) really more efficient than COUNTIF(K81:O81,5)? http://www.mrexcel.com/board2/viewtopic.php?t=40233 compares their temporal profile. With ca 20,000 cells in MainList, I could have figured that ISNUMBER(MATCH()) would be quicker, but how about for the 5-cell range K81:O81? Context matters. |
Harlan Grove wrote:
"Aladin Akyurek" wrote... Harlan Grove wrote: ... Is ISNUMBER(MATCH(5,K81:O81,0)) really more efficient than COUNTIF(K81:O81,5)? http://www.mrexcel.com/board2/viewtopic.php?t=40233 compares their temporal profile. With ca 20,000 cells in MainList, I could have figured that ISNUMBER(MATCH()) would be quicker, but how about for the 5-cell range K81:O81? Context matters. Generalization (as human mind is disposed to) also matters. An idiom gets adopted across all contexts very easily. And yes, IsNumber|Match over such small ranges is still as good as CountIf and has better volatility score. |
Thanks very much, line return found. Didnt think of that one !
"Domenic" wrote in message ... In article , "Phil" wrote: Contains error. i cant find it! If you copied and pasted the formula into your spreadsheet, make sure you don't have a line return or additional spaces inserted in the formula. |
| All times are GMT +1. The time now is 04:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com