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"
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" wrote in message
...
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
__________________________