You can only use full column references in a SUMPRODUCT formula if you have
Excel 2007. For earlier versions you will have to refer to it as something
like:

=SUMPRODUCT(('Data only'!A2:A65536="apples")*('Data only'!G2:G65536="red"))

The ranges do not have to be in the same worksheet.

Hope this helps.

Pete

"Rae" wrote in message
...
Good morning,

Thank you to everyone who responded. And all of these functions worked in
test, except when I tried to use against real production data none of
these
functions worked for me.

What I am trying to do is count records that meets the criteria that I am
interrogating. The data that I am trying to interrogate exists within the
same workbook as the calculation but in a seperate worksheet from the
calculation. And I wish to set the calculation up to interrogate the
entire
column, so as the data grows the calculation does not have to be updated
or
changed because it should already be checking the entire column.

See below:
=SUMPRODUCT(('Data only'!A:A="apples")*('Data only'!G:G="red"))

Does the data and calculation have to exist within the same worksheet to
work?

Thank you



"Rae" wrote:

I want to count the number of record/rows that have a true condition when
column A and Column D equals "red apples"; how do I do this?

Example:
cell range a2 - a4 = apples
cell range a5 - a6 = pears
cell range a7 - a8 = apples
AND
cell range d2 = green
cell range d3 - d5 = red
cell range d6 - d7 = green
cell range d8 = red

I would expect to have a returned count value of 3 records/rows where
data
is "red apples".....make sense?

Thanks