Help

P

Phil

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
 
D

Domenic

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!
 
P

Phil

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.
 
D

Domenic

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!
 
P

Phil

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
 
A

Aladin Akyurek

=IF(ISNUMBER(MATCH(5,K81:O81,0)),COUNTIF(INDEX($B$5:$F$10,0,MATCH(5,K81:O81,0))),"")
 
D

Domenic

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!
 
P

Phil

Contains error.
i cant find it!
Domenic said:
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!
Click to expand...
 
D

Domenic

Phil said:
Contains error.
i cant find it!
Click to expand...

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.
 
H

Harlan Grove

Domenic said:
I think Aladin meant...

=IF(ISNUMBER(MATCH(5,K81:O81,0)),COUNTIF(INDEX($B$5:$F$10,0,
MATCH(5,K81:O81,0)),I92),"")
Click to expand...
....

Is ISNUMBER(MATCH(5,K81:O81,0)) really more efficient than
COUNTIF(K81:O81,5)?
 
A

Aladin Akyurek

Harlan said:
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.
Click to expand...

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.
 
Top