function to use to count value

[Rae]

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

 

[Tyro]

=SUMPRODUCT((A2:A8="apples")*(D28="red"))

Tyro

 

[Mike]

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

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

 

[T. Valko]

=SUM(IF(A2:A8="apples", IF(D28="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")*(D28="red"))

 

[Teethless mama]

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

 

[Rae]

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

 

[Tyro]

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

Tyro

 

[Pete_UK]

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]

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

 

[Pete_UK]

You're welcome - glad to have helped.

Pete

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