COUNTA in Array Formula

[Wim Ruepert]

I am trying to do a count of a number of text entries that meet a certain
category.

Range A1:A10 consists of the text entires to count such as:
A1 = Ceasar Salad
A2 = Tomato Soup
A3 = Monkey Salad
etc

Range B1:B10 specifies to which Category the dish belongs:

B1 = Salad
B2 = Soup
B3 = Salad
etc

How do I count the number of Salads in Column A ??

Say that cell D1 contains the category that I want to count the dishes for
(in this case "Salad")
I tried the following array formula:

{=COUNTA((B1:B10=D1)*A1:A10)}
This however does not give me the correct result

Similarly, the following formula does not work
=COUNTA(IF(I15:I22=B8,H15:H22))

What am I doing wrong ?
Can anybody help ??

Thanks very much

Wim

 

[Jason Morin]

Just use COUNTIF:

=COUNTIF(B1:B10,D1)

HTH
Jason
Atlanta, GA

 

[Wim Ruepert]

Thanks Jason but this doe not solve the problem
The nuumber of entries in range B1:B10 may not be the same as those in range
A1:A10. I really need to count the entries in A1:A10,based on the criteria
in B1:B10

Any other suggestions ??

thanks for your help

Wim

 

[Leo Heuser]

Wim

Try this one:

=SUMPRODUCT(NOT(ISERROR(SEARCH(" "&D1&" "," "&A1:A10&" ")))+0)

The formula will find what's in D1, if it exists at the start of, inside or
at the end of the cells A1:A10, but it won't find D1, if it's part of
another word, e.g. Mexican Salad will be found, but Mexisalad will not.
If you also want to find, when inside another word use:

=SUMPRODUCT(NOT(ISERROR(SEARCH(D1,A1:A10)))+0)

instead.

 

[Wim Ruepert]

Thank you Leo
Unfortunately, not all dishes in range A1Ä10 contain the term of the
category, i.e not all salads contain the word "salad", and not all
main-courses contain the word "main-course".

Nice formula though !!, will certainly use this in other applications.
thanks again
Wim

 

[Leo Heuser]

You're welcome, Wim.
I appreciate your feedback.

Your answer makes me wonder, how your
list is organized.

Your answer to Jason indicates, that the number
of entries in column A may be different from
the number of entries in column B. Data in
e.g. A1:A100 and B1:B10. E.g. 10 different
salads in A1:A100, but the word "Salad" only
once in B1:B10? Apparently not, because
your example shows "Salad" in B1 and B3!?

How do you tell to which category
an entry in A1:A100 belongs, if the entry
doesn't contain the term of the category?

 

[Wim Ruepert]

The dishes of a restaurant menu are listed in Column A
When entering the dishes, the user selects a categories that the dish
belongs to in column B. These categories are selected from a list and
include "Entree", "Main Course", "Deserts", etc. So when a user enters in
column A a dish called "chocolate cake" he will have to select in column B
the "desert"category.

The application will need to count the number of dishes that have been
entered within each category. i.e how many deserts are in the list, and
how many entrees. Hence I need to count the number of entries in column A,
based on the criteria in Column B.

Hope this clarifies the challenge. Do you know how to do this ??

Thanks
Wim

 

[Leo Heuser]

If I understand you properly, you are
interested in the number of *distinct*
values in column A based on the
criteria in column B.

For example if A2:A7 contains a,a,b,a,c,d
and B2:B7 contains 3,3,2,3,1,3
and if D1 contains 3, the number you're
looking for is 2 (3 a's + 1 d, but "a" only counts
as one, so 1+1).

Under those circumstances this formula will
do the job:

=SUMPRODUCT((COUNTIF(OFFSET(A2,0,0,ROW(
INDIRECT("2:10"))-1),A2:A10)=1)*(B2:B10=D1))