Countif
 

Question:

I have a lookup table:

A
Fruits:
Apple
Orange

B
Dairy:
Milk
Cheese

And I have a list:

Milk
Orange
Cheese

I want to set up a formula to count from the list:

# of Fruit
and
# of Dairy

Please help.

Countif
 

With the list in D1:D3

Fruits in A2:A3
Dairy in B2:B3

For the count of fruits:

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A3,$D1:$D3,0))))

Copy across to get the count of dairy

Biff

"Sweepea" wrote in message
...
Question:

I have a lookup table:

A
Fruits:
Apple
Orange

B
Dairy:
Milk
Cheese

And I have a list:

Milk
Orange
Cheese

I want to set up a formula to count from the list:

# of Fruit
and
# of Dairy

Please help.

Countif
 

Thanks, Biff.

This formula helps but it's not counting same fruit or dairy items that
appear twice.

Example, the list could be:

Apple
Apple
Orange

This formula only return a count of 2 which should really be 3.

Please advise. Thank you very much.

"T. Valko" wrote:

With the list in D1:D3

Fruits in A2:A3
Dairy in B2:B3

For the count of fruits:

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A3,$D1:$D3,0))))

Copy across to get the count of dairy

Biff

"Sweepea" wrote in message
...
Question:

I have a lookup table:

A
Fruits:
Apple
Orange

B
Dairy:
Milk
Cheese

And I have a list:

Milk
Orange
Cheese

I want to set up a formula to count from the list:

# of Fruit
and
# of Dairy

Please help.

Countif
 

Just reverse the ranges in the Match function:

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A3,$D1:$D3,0))))

=SUMPRODUCT(--(ISNUMBER(MATCH($D1:$D3,A2:A3,0))))

Biff

"Sweepea" wrote in message
...
Thanks, Biff.

This formula helps but it's not counting same fruit or dairy items that
appear twice.

Example, the list could be:

Apple
Apple
Orange

This formula only return a count of 2 which should really be 3.

Please advise. Thank you very much.

"T. Valko" wrote:

With the list in D1:D3

Fruits in A2:A3
Dairy in B2:B3

For the count of fruits:

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A3,$D1:$D3,0))))

Copy across to get the count of dairy

Biff

"Sweepea" wrote in message
...
Question:

I have a lookup table:

A
Fruits:
Apple
Orange

B
Dairy:
Milk
Cheese

And I have a list:

Milk
Orange
Cheese

I want to set up a formula to count from the list:

# of Fruit
and
# of Dairy

Please help.

Countif
 

Thank you very much. It works!!


"T. Valko" wrote:

Just reverse the ranges in the Match function:

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A3,$D1:$D3,0))))

=SUMPRODUCT(--(ISNUMBER(MATCH($D1:$D3,A2:A3,0))))

Biff

"Sweepea" wrote in message
...
Thanks, Biff.

This formula helps but it's not counting same fruit or dairy items that
appear twice.

Example, the list could be:

Apple
Apple
Orange

This formula only return a count of 2 which should really be 3.

Please advise. Thank you very much.

"T. Valko" wrote:

With the list in D1:D3

Fruits in A2:A3
Dairy in B2:B3

For the count of fruits:

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A3,$D1:$D3,0))))

Copy across to get the count of dairy

Biff

"Sweepea" wrote in message
...
Question:

I have a lookup table:

A
Fruits:
Apple
Orange

B
Dairy:
Milk
Cheese

And I have a list:

Milk
Orange
Cheese

I want to set up a formula to count from the list:

# of Fruit
and
# of Dairy

Please help.

Countif
 

You're welcome. Thanks for the feedback!

Biff

"Sweepea" wrote in message
...
Thank you very much. It works!!


"T. Valko" wrote:

Just reverse the ranges in the Match function:

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A3,$D1:$D3,0))))

=SUMPRODUCT(--(ISNUMBER(MATCH($D1:$D3,A2:A3,0))))

Biff

"Sweepea" wrote in message
...
Thanks, Biff.

This formula helps but it's not counting same fruit or dairy items that
appear twice.

Example, the list could be:

Apple
Apple
Orange

This formula only return a count of 2 which should really be 3.

Please advise. Thank you very much.

"T. Valko" wrote:

With the list in D1:D3

Fruits in A2:A3
Dairy in B2:B3

For the count of fruits:

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A3,$D1:$D3,0))))

Copy across to get the count of dairy

Biff

"Sweepea" wrote in message
...
Question:

I have a lookup table:

A
Fruits:
Apple
Orange

B
Dairy:
Milk
Cheese

And I have a list:

Milk
Orange
Cheese

I want to set up a formula to count from the list:

# of Fruit
and
# of Dairy

Please help.