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