function to use to count value
 

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

function to use to count value
 

=SUMPRODUCT((A2:A8="apples")*(D2:D8="red"))

Tyro

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

function to use to count value
 

In Excel 2007:
=COUNTIFS(A2:A8,"apples",D2:D8,"red")

In Excel 2003:
=SUM(IF(A2:A8="apples", IF(D2:D8="red",1,0), 0))
*enter this one as an array formula (instead of hitting enter, hit
ctrl+shift+enter)*


"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

function to use to count value
 

=SUM(IF(A2:A8="apples", IF(D2:D8="red",1,0), 0))
*enter this one as an array formula

You'll get the same result and save a few keystrokes: (array enterd)

=SUM((A2:A8="apples")*(D2:D8="red"))


--
Biff
Microsoft Excel MVP


"Mike" wrote in message
...
In Excel 2007:
=COUNTIFS(A2:A8,"apples",D2:D8,"red")

In Excel 2003:
=SUM(IF(A2:A8="apples", IF(D2:D8="red",1,0), 0))
*enter this one as an array formula (instead of hitting enter, hit
ctrl+shift+enter)*


"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

function to use to count value
 

=COUNT(INDEX(SEARCH("apples",A2:A8)/(D2:D8="red"),0))

"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

function to use to count value
 

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

function to use to count value
 

The formula can be on any worksheet. You don't state how the formula does
not work for you.

Tyro

"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

function to use to count value
 

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

function to use to count value
 

Thank you!!!! The explanation of sumproduct limitations for earlier versions
of excel solved my problem.



"Pete_UK" wrote:

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

function to use to count value
 

You're welcome - glad to have helped.

Pete

"Rae" wrote in message
...
Thank you!!!! The explanation of sumproduct limitations for earlier
versions
of excel solved my problem.



"Pete_UK" wrote:

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