I need to count the instances on the word "Trane" in a range of cells, but
base the countif statement on the information in another range of cells. If
the parameter range has a "N" in it, then count the corresponding cell in the
counting range with "Trane" in it. If not, don't count it. This needs to be
a formula entered in one cell that displays the results of the countif. I
have tried this formula:
=COUNT(IF(($A$2:$A$297="N")*($O$2:$O$297="Trane"), $O$2:$O$297))
that is shown in the help section, but it only returns zeros when I use it.
Any help would be greatly appreciated!
-Kai

I need to count the instances on the word "Trane" in a range of cells, but
base the countif statement on the information in another range of cells.
If
the parameter range has a "N" in it, then count the corresponding cell in
the
counting range with "Trane" in it. If not, don't count it. This needs to
be
a formula entered in one cell that displays the results of the countif. I
have tried this formula:
=COUNT(IF(($A$2:$A$297="N")*($O$2:$O$297="Trane"), $O$2:$O$297))
that is shown in the help section, but it only returns zeros when I use
it.
Any help would be greatly appreciated!

Give this formula a try...

=SUMPRODUCT(--($A$2:$A$297="N"),--($O$2:$O$297="Trane"))

Rick

You can do it with SumProduct... check out this link.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"Kai Cunningham" wrote:

I need to count the instances on the word "Trane" in a range of cells, but
base the countif statement on the information in another range of cells. If
the parameter range has a "N" in it, then count the corresponding cell in the
counting range with "Trane" in it. If not, don't count it. This needs to be
a formula entered in one cell that displays the results of the countif. I
have tried this formula:
=COUNT(IF(($A$2:$A$297="N")*($O$2:$O$297="Trane"), $O$2:$O$297))
that is shown in the help section, but it only returns zeros when I use it.
Any help would be greatly appreciated!
-Kai

Kai,

Here's one way:

=SUMPRODUCT(($A$2:$A$297="N")*($O$2:$O$297="Trane" ))


--
Hope that helps.

Vergel Adriano


"Kai Cunningham" wrote:

I need to count the instances on the word "Trane" in a range of cells, but
base the countif statement on the information in another range of cells. If
the parameter range has a "N" in it, then count the corresponding cell in the
counting range with "Trane" in it. If not, don't count it. This needs to be
a formula entered in one cell that displays the results of the countif. I
have tried this formula:
=COUNT(IF(($A$2:$A$297="N")*($O$2:$O$297="Trane"), $O$2:$O$297))
that is shown in the help section, but it only returns zeros when I use it.
Any help would be greatly appreciated!
-Kai

Thanks a ton for the quick responses! Worked like a charm!
-Kai

"Vergel Adriano" wrote:

Kai,

Here's one way:

=SUMPRODUCT(($A$2:$A$297="N")*($O$2:$O$297="Trane" ))


--
Hope that helps.

Vergel Adriano


"Kai Cunningham" wrote:

I need to count the instances on the word "Trane" in a range of cells, but
base the countif statement on the information in another range of cells. If
the parameter range has a "N" in it, then count the corresponding cell in the
counting range with "Trane" in it. If not, don't count it. This needs to be
a formula entered in one cell that displays the results of the countif. I
have tried this formula:
=COUNT(IF(($A$2:$A$297="N")*($O$2:$O$297="Trane"), $O$2:$O$297))
that is shown in the help section, but it only returns zeros when I use it.
Any help would be greatly appreciated!
-Kai