How:iserror vlookup & count no. times value shows with conditions

[Pauline]

How do I use an Iserror Vlookup to count how many time a value appears,
subject to another column having specific data in it.

[Peo Sjoblom]

Use COUNTIF to count values with one condition and SUMPRODUCT to count with
more than one condition

=COUNTIF(A2:A100,B2)

will count the value in cell B2 in A2:A100

=SUMPRODUCT(--(A2:A100=B2),--(C2:C100=D1))

will count B2 in A2:A100 and D2 in C2:C100

to remove #N/A from VLOOKUP result use

=IF(ISNA(MATCH(B2,D2:D100,0)),"",VLOOKUP(

Regards,

Peo Sjoblom



"Pauline" <[email protected]> wrote in message
news:[email protected]...
> How do I use an Iserror Vlookup to count how many time a value appears,
> subject to another column having specific data in it.

[Pauline]

I do not think this will work

I have two worksheets.
Worksheet 1 is where I want to have my calculation based on the number of
times a word appears in a defined Name range in another worksheet (worksheet
2), but only count this if the month appears in another defined range
(column) on worksheet 2.

Therefore worksheet 1 could have the following:
apples
pears
oranges
Worksheet 2 could have data with column 4 showing names of fruit and column
6 showing the months of the year.
In worksheet 1 I want to ask excel to count how many times apples appear but
only count them if the month is January.

Hope this is clear. Any help to resolve this would be much appreciated.

Pauline

"Peo Sjoblom" wrote:

> Use COUNTIF to count values with one condition and SUMPRODUCT to count with
> more than one condition
>
> =COUNTIF(A2:A100,B2)
>
> will count the value in cell B2 in A2:A100
>
> =SUMPRODUCT(--(A2:A100=B2),--(C2:C100=D1))
>
> will count B2 in A2:A100 and D2 in C2:C100
>
> to remove #N/A from VLOOKUP result use
>
> =IF(ISNA(MATCH(B2,D2:D100,0)),"",VLOOKUP(
>
> Regards,
>
> Peo Sjoblom
>
>
>
> "Pauline" <[email protected]> wrote in message
> news:[email protected]...
> > How do I use an Iserror Vlookup to count how many time a value appears,
> > subject to another column having specific data in it.
>
>
>

[Richard Buttrey]

Assuming data in column 4 sheet 2 is named "Fruit", and data in column
6 sheet is named "Months", and both ranges are contained within the
same rows. Then with apples, pears and oranges in A1:A3 on sheet 1,
enter in B1

=SUMPRODUCT((fruit=A1)*(months="January"))
and copy down.

HTH



On Fri, 14 Oct 2005 08:39:07 -0700, "Pauline"
<[email protected]> wrote:

>I do not think this will work
>
>I have two worksheets.
>Worksheet 1 is where I want to have my calculation based on the number of
>times a word appears in a defined Name range in another worksheet (worksheet
>2), but only count this if the month appears in another defined range
>(column) on worksheet 2.
>
>Therefore worksheet 1 could have the following:
>apples
>pears
>oranges
>Worksheet 2 could have data with column 4 showing names of fruit and column
>6 showing the months of the year.
>In worksheet 1 I want to ask excel to count how many times apples appear but
>only count them if the month is January.
>
>Hope this is clear. Any help to resolve this would be much appreciated.
>
>Pauline
>
>"Peo Sjoblom" wrote:
>
>> Use COUNTIF to count values with one condition and SUMPRODUCT to count with
>> more than one condition
>>
>> =COUNTIF(A2:A100,B2)
>>
>> will count the value in cell B2 in A2:A100
>>
>> =SUMPRODUCT(--(A2:A100=B2),--(C2:C100=D1))
>>
>> will count B2 in A2:A100 and D2 in C2:C100
>>
>> to remove #N/A from VLOOKUP result use
>>
>> =IF(ISNA(MATCH(B2,D2:D100,0)),"",VLOOKUP(
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>>
>> "Pauline" <[email protected]> wrote in message
>> news:[email protected]...
>> > How do I use an Iserror Vlookup to count how many time a value appears,
>> > subject to another column having specific data in it.
>>
>>
>>

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

[Peo Sjoblom]

Use the sumproduct adapted to your data

=SUMPRODUCT(--(A2:A100=B2),--(MONTH(C2:C100)=D1))

where B2 would hold the fruit and D1 the month number

Regards,

Peo Sjoblom



"Pauline" <[email protected]> wrote in message
news:[email protected]...
> I do not think this will work
>
> I have two worksheets.
> Worksheet 1 is where I want to have my calculation based on the number of
> times a word appears in a defined Name range in another worksheet
(worksheet
> 2), but only count this if the month appears in another defined range
> (column) on worksheet 2.
>
> Therefore worksheet 1 could have the following:
> apples
> pears
> oranges
> Worksheet 2 could have data with column 4 showing names of fruit and
column
> 6 showing the months of the year.
> In worksheet 1 I want to ask excel to count how many times apples appear
but
> only count them if the month is January.
>
> Hope this is clear. Any help to resolve this would be much appreciated.
>
> Pauline
>
> "Peo Sjoblom" wrote:
>
> > Use COUNTIF to count values with one condition and SUMPRODUCT to count
with
> > more than one condition
> >
> > =COUNTIF(A2:A100,B2)
> >
> > will count the value in cell B2 in A2:A100
> >
> > =SUMPRODUCT(--(A2:A100=B2),--(C2:C100=D1))
> >
> > will count B2 in A2:A100 and D2 in C2:C100
> >
> > to remove #N/A from VLOOKUP result use
> >
> > =IF(ISNA(MATCH(B2,D2:D100,0)),"",VLOOKUP(
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> >
> >
> > "Pauline" <[email protected]> wrote in message
> > news:[email protected]...
> > > How do I use an Iserror Vlookup to count how many time a value
appears,
> > > subject to another column having specific data in it.
> >
> >
> >

[Pauline]

Still no joy. I have #NUM error message
Here is another example.

Worksheet 1
Fruits Jan Feb Mar April May
apple #NUM!
pear
apple
banana
Orange

Worksheet 2
Name Fruit Month
Fred apple 1
Jo apple 1
Alan pear 2
Pete orange 1

I have defined the column Fruit as Fruit and Month as Month.
My formula is:
=SUMPRODUCT((Fruit=Sheet1!A3)*(Month=1))

I get #NUM! error message

By the way, does it matter that I am still using excel 2000 for this
formulae on another machine.

Regards

Pauline


"Richard Buttrey" wrote:

> Assuming data in column 4 sheet 2 is named "Fruit", and data in column
> 6 sheet is named "Months", and both ranges are contained within the
> same rows. Then with apples, pears and oranges in A1:A3 on sheet 1,
> enter in B1
>
> =SUMPRODUCT((fruit=A1)*(months="January"))
> and copy down.
>
> HTH
>
>
>
> On Fri, 14 Oct 2005 08:39:07 -0700, "Pauline"
> <[email protected]> wrote:
>
> >I do not think this will work
> >
> >I have two worksheets.
> >Worksheet 1 is where I want to have my calculation based on the number of
> >times a word appears in a defined Name range in another worksheet (worksheet
> >2), but only count this if the month appears in another defined range
> >(column) on worksheet 2.
> >
> >Therefore worksheet 1 could have the following:
> >apples
> >pears
> >oranges
> >Worksheet 2 could have data with column 4 showing names of fruit and column
> >6 showing the months of the year.
> >In worksheet 1 I want to ask excel to count how many times apples appear but
> >only count them if the month is January.
> >
> >Hope this is clear. Any help to resolve this would be much appreciated.
> >
> >Pauline
> >
> >"Peo Sjoblom" wrote:
> >
> >> Use COUNTIF to count values with one condition and SUMPRODUCT to count with
> >> more than one condition
> >>
> >> =COUNTIF(A2:A100,B2)
> >>
> >> will count the value in cell B2 in A2:A100
> >>
> >> =SUMPRODUCT(--(A2:A100=B2),--(C2:C100=D1))
> >>
> >> will count B2 in A2:A100 and D2 in C2:C100
> >>
> >> to remove #N/A from VLOOKUP result use
> >>
> >> =IF(ISNA(MATCH(B2,D2:D100,0)),"",VLOOKUP(
> >>
> >> Regards,
> >>
> >> Peo Sjoblom
> >>
> >>
> >>
> >> "Pauline" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > How do I use an Iserror Vlookup to count how many time a value appears,
> >> > subject to another column having specific data in it.
> >>
> >>
> >>
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________
>

[Peo Sjoblom]

Excel 2000 wouldn't matter, I suspect you made an error when you defined the
names, they need to be of the same size, are they?
Are the months in sheet2 numeric integers (not that it would prevent the
error but it would not pickup the number if they are text)?
First check that the defined names are using absolute references (dollar
signs like Sheet1!$A2$A$50) and that if you use A2:A50 then the month needs
to be the same size or else you'll get NUM error

Regards,

Peo Sjoblom

"Pauline" <[email protected]> wrote in message
news:[email protected]...
> Still no joy. I have #NUM error message
> Here is another example.
>
> Worksheet 1
> Fruits Jan Feb Mar April May
> apple #NUM!
> pear
> apple
> banana
> Orange
>
> Worksheet 2
> Name Fruit Month
> Fred apple 1
> Jo apple 1
> Alan pear 2
> Pete orange 1
>
> I have defined the column Fruit as Fruit and Month as Month.
> My formula is:
> =SUMPRODUCT((Fruit=Sheet1!A3)*(Month=1))
>
> I get #NUM! error message
>
> By the way, does it matter that I am still using excel 2000 for this
> formulae on another machine.
>
> Regards
>
> Pauline
>
>
> "Richard Buttrey" wrote:
>
> > Assuming data in column 4 sheet 2 is named "Fruit", and data in column
> > 6 sheet is named "Months", and both ranges are contained within the
> > same rows. Then with apples, pears and oranges in A1:A3 on sheet 1,
> > enter in B1
> >
> > =SUMPRODUCT((fruit=A1)*(months="January"))
> > and copy down.
> >
> > HTH
> >
> >
> >
> > On Fri, 14 Oct 2005 08:39:07 -0700, "Pauline"
> > <[email protected]> wrote:
> >
> > >I do not think this will work
> > >
> > >I have two worksheets.
> > >Worksheet 1 is where I want to have my calculation based on the number
of
> > >times a word appears in a defined Name range in another worksheet
(worksheet
> > >2), but only count this if the month appears in another defined range
> > >(column) on worksheet 2.
> > >
> > >Therefore worksheet 1 could have the following:
> > >apples
> > >pears
> > >oranges
> > >Worksheet 2 could have data with column 4 showing names of fruit and
column
> > >6 showing the months of the year.
> > >In worksheet 1 I want to ask excel to count how many times apples
appear but
> > >only count them if the month is January.
> > >
> > >Hope this is clear. Any help to resolve this would be much appreciated.
> > >
> > >Pauline
> > >
> > >"Peo Sjoblom" wrote:
> > >
> > >> Use COUNTIF to count values with one condition and SUMPRODUCT to
count with
> > >> more than one condition
> > >>
> > >> =COUNTIF(A2:A100,B2)
> > >>
> > >> will count the value in cell B2 in A2:A100
> > >>
> > >> =SUMPRODUCT(--(A2:A100=B2),--(C2:C100=D1))
> > >>
> > >> will count B2 in A2:A100 and D2 in C2:C100
> > >>
> > >> to remove #N/A from VLOOKUP result use
> > >>
> > >> =IF(ISNA(MATCH(B2,D2:D100,0)),"",VLOOKUP(
> > >>
> > >> Regards,
> > >>
> > >> Peo Sjoblom
> > >>
> > >>
> > >>
> > >> "Pauline" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> > How do I use an Iserror Vlookup to count how many time a value
appears,
> > >> > subject to another column having specific data in it.
> > >>
> > >>
> > >>
> >
> > __
> > Richard Buttrey
> > Grappenhall, Cheshire, UK
> > __________________________
> >

[Pauline]

I am afraid it still does not work.

The Fruit is a character field and the month is a number. Is this the
problem ?

Pauline

"Peo Sjoblom" wrote:

> Excel 2000 wouldn't matter, I suspect you made an error when you defined the
> names, they need to be of the same size, are they?
> Are the months in sheet2 numeric integers (not that it would prevent the
> error but it would not pickup the number if they are text)?
> First check that the defined names are using absolute references (dollar
> signs like Sheet1!$A2$A$50) and that if you use A2:A50 then the month needs
> to be the same size or else you'll get NUM error
>
> Regards,
>
> Peo Sjoblom
>
> "Pauline" <[email protected]> wrote in message
> news:[email protected]...
> > Still no joy. I have #NUM error message
> > Here is another example.
> >
> > Worksheet 1
> > Fruits Jan Feb Mar April May
> > apple #NUM!
> > pear
> > apple
> > banana
> > Orange
> >
> > Worksheet 2
> > Name Fruit Month
> > Fred apple 1
> > Jo apple 1
> > Alan pear 2
> > Pete orange 1
> >
> > I have defined the column Fruit as Fruit and Month as Month.
> > My formula is:
> > =SUMPRODUCT((Fruit=Sheet1!A3)*(Month=1))
> >
> > I get #NUM! error message
> >
> > By the way, does it matter that I am still using excel 2000 for this
> > formulae on another machine.
> >
> > Regards
> >
> > Pauline
> >
> >
> > "Richard Buttrey" wrote:
> >
> > > Assuming data in column 4 sheet 2 is named "Fruit", and data in column
> > > 6 sheet is named "Months", and both ranges are contained within the
> > > same rows. Then with apples, pears and oranges in A1:A3 on sheet 1,
> > > enter in B1
> > >
> > > =SUMPRODUCT((fruit=A1)*(months="January"))
> > > and copy down.
> > >
> > > HTH
> > >
> > >
> > >
> > > On Fri, 14 Oct 2005 08:39:07 -0700, "Pauline"
> > > <[email protected]> wrote:
> > >
> > > >I do not think this will work
> > > >
> > > >I have two worksheets.
> > > >Worksheet 1 is where I want to have my calculation based on the number
> of
> > > >times a word appears in a defined Name range in another worksheet
> (worksheet
> > > >2), but only count this if the month appears in another defined range
> > > >(column) on worksheet 2.
> > > >
> > > >Therefore worksheet 1 could have the following:
> > > >apples
> > > >pears
> > > >oranges
> > > >Worksheet 2 could have data with column 4 showing names of fruit and
> column
> > > >6 showing the months of the year.
> > > >In worksheet 1 I want to ask excel to count how many times apples
> appear but
> > > >only count them if the month is January.
> > > >
> > > >Hope this is clear. Any help to resolve this would be much appreciated.
> > > >
> > > >Pauline
> > > >
> > > >"Peo Sjoblom" wrote:
> > > >
> > > >> Use COUNTIF to count values with one condition and SUMPRODUCT to
> count with
> > > >> more than one condition
> > > >>
> > > >> =COUNTIF(A2:A100,B2)
> > > >>
> > > >> will count the value in cell B2 in A2:A100
> > > >>
> > > >> =SUMPRODUCT(--(A2:A100=B2),--(C2:C100=D1))
> > > >>
> > > >> will count B2 in A2:A100 and D2 in C2:C100
> > > >>
> > > >> to remove #N/A from VLOOKUP result use
> > > >>
> > > >> =IF(ISNA(MATCH(B2,D2:D100,0)),"",VLOOKUP(
> > > >>
> > > >> Regards,
> > > >>
> > > >> Peo Sjoblom
> > > >>
> > > >>
> > > >>
> > > >> "Pauline" <[email protected]> wrote in message
> > > >> news:[email protected]...
> > > >> > How do I use an Iserror Vlookup to count how many time a value
> appears,
> > > >> > subject to another column having specific data in it.
> > > >>
> > > >>
> > > >>
> > >
> > > __
> > > Richard Buttrey
> > > Grappenhall, Cheshire, UK
> > > __________________________
> > >
>
>
>

[Peo Sjoblom]

You still get the error? Just try with a smaller scale without using defined
names, let's say

=SUMPRODUCT(--('Worksheet 2'!A2:A10="Apple"),--('Worksheet 2'!B2:B10=1))

adapt to fit you sheet names, then if it works it must be an error in the
defined names. If you get an answer you didn't expect you might want to
check for leading and traling spaces in the text

Regards,

Peo Sjoblom


"Pauline" <[email protected]> wrote in message
news:[email protected]...
> I am afraid it still does not work.
>
> The Fruit is a character field and the month is a number. Is this the
> problem ?
>
> Pauline
>
> "Peo Sjoblom" wrote:
>
> > Excel 2000 wouldn't matter, I suspect you made an error when you defined
the
> > names, they need to be of the same size, are they?
> > Are the months in sheet2 numeric integers (not that it would prevent the
> > error but it would not pickup the number if they are text)?
> > First check that the defined names are using absolute references (dollar
> > signs like Sheet1!$A2$A$50) and that if you use A2:A50 then the month
needs
> > to be the same size or else you'll get NUM error
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > "Pauline" <[email protected]> wrote in message
> > news:[email protected]...
> > > Still no joy. I have #NUM error message
> > > Here is another example.
> > >
> > > Worksheet 1
> > > Fruits Jan Feb Mar April May
> > > apple #NUM!
> > > pear
> > > apple
> > > banana
> > > Orange
> > >
> > > Worksheet 2
> > > Name Fruit Month
> > > Fred apple 1
> > > Jo apple 1
> > > Alan pear 2
> > > Pete orange 1
> > >
> > > I have defined the column Fruit as Fruit and Month as Month.
> > > My formula is:
> > > =SUMPRODUCT((Fruit=Sheet1!A3)*(Month=1))
> > >
> > > I get #NUM! error message
> > >
> > > By the way, does it matter that I am still using excel 2000 for this
> > > formulae on another machine.
> > >
> > > Regards
> > >
> > > Pauline
> > >
> > >
> > > "Richard Buttrey" wrote:
> > >
> > > > Assuming data in column 4 sheet 2 is named "Fruit", and data in
column
> > > > 6 sheet is named "Months", and both ranges are contained within the
> > > > same rows. Then with apples, pears and oranges in A1:A3 on sheet 1,
> > > > enter in B1
> > > >
> > > > =SUMPRODUCT((fruit=A1)*(months="January"))
> > > > and copy down.
> > > >
> > > > HTH
> > > >
> > > >
> > > >
> > > > On Fri, 14 Oct 2005 08:39:07 -0700, "Pauline"
> > > > <[email protected]> wrote:
> > > >
> > > > >I do not think this will work
> > > > >
> > > > >I have two worksheets.
> > > > >Worksheet 1 is where I want to have my calculation based on the
number
> > of
> > > > >times a word appears in a defined Name range in another worksheet
> > (worksheet
> > > > >2), but only count this if the month appears in another defined
range
> > > > >(column) on worksheet 2.
> > > > >
> > > > >Therefore worksheet 1 could have the following:
> > > > >apples
> > > > >pears
> > > > >oranges
> > > > >Worksheet 2 could have data with column 4 showing names of fruit
and
> > column
> > > > >6 showing the months of the year.
> > > > >In worksheet 1 I want to ask excel to count how many times apples
> > appear but
> > > > >only count them if the month is January.
> > > > >
> > > > >Hope this is clear. Any help to resolve this would be much
appreciated.
> > > > >
> > > > >Pauline
> > > > >
> > > > >"Peo Sjoblom" wrote:
> > > > >
> > > > >> Use COUNTIF to count values with one condition and SUMPRODUCT to
> > count with
> > > > >> more than one condition
> > > > >>
> > > > >> =COUNTIF(A2:A100,B2)
> > > > >>
> > > > >> will count the value in cell B2 in A2:A100
> > > > >>
> > > > >> =SUMPRODUCT(--(A2:A100=B2),--(C2:C100=D1))
> > > > >>
> > > > >> will count B2 in A2:A100 and D2 in C2:C100
> > > > >>
> > > > >> to remove #N/A from VLOOKUP result use
> > > > >>
> > > > >> =IF(ISNA(MATCH(B2,D2:D100,0)),"",VLOOKUP(
> > > > >>
> > > > >> Regards,
> > > > >>
> > > > >> Peo Sjoblom
> > > > >>
> > > > >>
> > > > >>
> > > > >> "Pauline" <[email protected]> wrote in message
> > > > >> news:[email protected]...
> > > > >> > How do I use an Iserror Vlookup to count how many time a value
> > appears,
> > > > >> > subject to another column having specific data in it.
> > > > >>
> > > > >>
> > > > >>
> > > >
> > > > __
> > > > Richard Buttrey
> > > > Grappenhall, Cheshire, UK
> > > > __________________________
> > > >
> >
> >
> >