countif & subtotal functions
 

I need the ability of both of these functions but combined. I would like to
be able to count if All or Tops appears in my filtered list without having to
filter on the column that contains these values.

countif & subtotal functions
 

One possible way:

If there might be other words in the cell in addition to "All" or "Tops"
then you can use a wildcard as follows.
=COUNTIF(A1:A20,"*All*")+COUNTIF(A1:A20,"*Tops*")

However, you mentioned this was already a filtered list. Your results will
include the filtered data if the criteria matches.
If the cells that contain "All" or "Tops" will not have any other words in
them, then you should be able to use SUMPRODUCT.
You'll need to include the criteria that you used to filter your list.

=SUMPRODUCT(--(A1:A20="All")+(A1:A20="*Tops*"),--(B1:B20="FilteredCriteria"))

HTH,
Paul

--

"sot" wrote in message
...
I need the ability of both of these functions but combined. I would like
to
be able to count if All or Tops appears in my filtered list without having
to
filter on the column that contains these values.

countif & subtotal functions
 

But if the cell contains both All and Tops, won't your formulae double-count
them?

And isn't your first double unary minus in the SUMPRODUCT unnecessary, as
the addition would already do the coercing of booleans to a number?
--
David Biddulph

"PCLIVE" wrote in message
...
One possible way:

If there might be other words in the cell in addition to "All" or "Tops"
then you can use a wildcard as follows.
=COUNTIF(A1:A20,"*All*")+COUNTIF(A1:A20,"*Tops*")

However, you mentioned this was already a filtered list. Your results
will include the filtered data if the criteria matches.
If the cells that contain "All" or "Tops" will not have any other words in
them, then you should be able to use SUMPRODUCT.
You'll need to include the criteria that you used to filter your list.

=SUMPRODUCT(--(A1:A20="All")+(A1:A20="*Tops*"),--(B1:B20="FilteredCriteria"))

HTH,
Paul

--

"sot" wrote in message
...
I need the ability of both of these functions but combined. I would like
to
be able to count if All or Tops appears in my filtered list without
having to
filter on the column that contains these values.

countif & subtotal functions
 

Good points David. I did not consider that both words may appear in the
same cell. However, I think it would be safe to say that the column would
not contain multiple words and therefore a SUMPRODUCT command would be the
way to go. I used the double urnary due to habbit. As you know, you are
correct that the addition takes care of converting booleans to a number.
But I also read somewhere that though using an operator such as 1*TRUE will
force the boolean to a number, the double urnary indicates to knowledgable
users that you are forcing a conversion and not necessarilly trying to
calculate something.

In any case, your point allowed me to notice a mistake in my formula.
It should be:
=SUMPRODUCT((A1:A20="All")+(A1:A20="Tops"),--(B1:B20="FilteredCriteria"))

Thanks for the information.
Regards,
Paul


--

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
But if the cell contains both All and Tops, won't your formulae
double-count them?

And isn't your first double unary minus in the SUMPRODUCT unnecessary, as
the addition would already do the coercing of booleans to a number?
--
David Biddulph

"PCLIVE" wrote in message
...
One possible way:

If there might be other words in the cell in addition to "All" or "Tops"
then you can use a wildcard as follows.
=COUNTIF(A1:A20,"*All*")+COUNTIF(A1:A20,"*Tops*")

However, you mentioned this was already a filtered list. Your results
will include the filtered data if the criteria matches.
If the cells that contain "All" or "Tops" will not have any other words
in them, then you should be able to use SUMPRODUCT.
You'll need to include the criteria that you used to filter your list.

=SUMPRODUCT(--(A1:A20="All")+(A1:A20="*Tops*"),--(B1:B20="FilteredCriteria"))

HTH,
Paul

--

"sot" wrote in message
...
I need the ability of both of these functions but combined. I would like
to
be able to count if All or Tops appears in my filtered list without
having to
filter on the column that contains these values.