Help

[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

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

In article <xu1fe.26946$G8.20079@text.news.blueyonder.co.uk>,
"Phil" <me@blueyonder.co.uk> 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

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

"Domenic" <domenic22@sympatico.ca> wrote in message
news:domenic22-486FB7.07422707052005@msnews.microsoft.com...
> 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 <xu1fe.26946$G8.20079@text.news.blueyonder.co.uk>,
> "Phil" <me@blueyonder.co.uk> 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

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

In article <domenic22-486FB7.07422707052005@msnews.microsoft.com>,
Domenic <domenic22@sympatico.ca> 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!

[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

"Domenic" <domenic22@sympatico.ca> wrote in message
news:domenic22-830117.07512807052005@msnews.microsoft.com...
> 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 <domenic22-486FB7.07422707052005@msnews.microsoft.com>,
> Domenic <domenic22@sympatico.ca> 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!

[Aladin Akyurek]

=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" <domenic22@sympatico.ca> wrote in message
> news:domenic22-830117.07512807052005@msnews.microsoft.com...
>
>>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 <domenic22-486FB7.07422707052005@msnews.microsoft.com>,
>>Domenic <domenic22@sympatico.ca> 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!
>
>
>

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

In article <427d06b0$0$15623$e4fe514c@news.xs4all.nl>,
Aladin Akyurek <akyurek@xs4all.nl> 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" <domenic22@sympatico.ca> wrote in message
> > news:domenic22-830117.07512807052005@msnews.microsoft.com...
> >
> >>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 <domenic22-486FB7.07422707052005@msnews.microsoft.com>,
> >>Domenic <domenic22@sympatico.ca> 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!
> >
> >
> >

[Phil]

Contains error.
i cant find it!
"Domenic" <domenic22@sympatico.ca> wrote in message
news:domenic22-2F98B4.15321107052005@msnews.microsoft.com...
>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 <427d06b0$0$15623$e4fe514c@news.xs4all.nl>,
> Aladin Akyurek <akyurek@xs4all.nl> 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" <domenic22@sympatico.ca> wrote in message
>> > news:domenic22-830117.07512807052005@msnews.microsoft.com...
>> >
>> >>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 <domenic22-486FB7.07422707052005@msnews.microsoft.com>,
>> >>Domenic <domenic22@sympatico.ca> 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!
>> >
>> >
>> >

[Domenic]

In article <QI9fe.27248$G8.18203@text.news.blueyonder.co.uk>,
"Phil" <me@blueyonder.co.uk> 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.

[Harlan Grove]

"Domenic" <domenic22@sympatico.ca> 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)?

[Aladin Akyurek]

Harlan Grove wrote:
> "Domenic" <domenic22@sympatico.ca> 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.

[Harlan Grove]

"Aladin Akyurek" <akyurek@xs4all.nl> 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.

[Aladin Akyurek]

Harlan Grove wrote:
> "Aladin Akyurek" <akyurek@xs4all.nl> 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.

[Phil]

Thanks very much, line return found. Didnt think of that one !
"Domenic" <domenic22@sympatico.ca> wrote in message
news:domenic22-1961DA.18360907052005@msnews.microsoft.com...
> In article <QI9fe.27248$G8.18203@text.news.blueyonder.co.uk>,
> "Phil" <me@blueyonder.co.uk> 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.