Going bananas trying to create a way to count multiple names in a column

Copper Contributor

Have a simple spreadsheet

in one column I have a bunch of fruit names about 30 different ones total input in about 250 rows

trying to do a count if 5 separate names to amount to the number 5 n a cell n a different column

have tried =countif(d2:d250”apple”, “orange”, “lemon”, “green grape”)

=sumcountifs(d2:d250{apple”, “orange”, “lemon”, “green grape”})

but no luck 
please, can someone help me!

 

 

5 Replies
the simplest way (conceptually) is
=countif(d2:d250,”apple”)+countif(d2:d250, “orange”)+countif(d2:d250,“lemon”)+countif(d2:d250,“green grape”)
another option using arrays is:
=sum(countif(d2:d250,{”apple”, “orange”, “lemon”, “green grape”}))

@mtarler 

thank you I will try 1st as sure 2nd choice is what I was trying but kept giving me error.   
will post tomorrow results 

and will try both just to see 

🥰

harshulz_0-1647918081866.png

=SUM(COUNTIF(A2:A12,"BANANA")+COUNTIF(A2:A12,"APPLE")+COUNTIF(A2:A12,"GRAPES")+COUNTIF(A2:A12,"KIWI")+COUNTIF(A2:A12,"KIWI"))

 

@Mumzee 

@mtarler 

For me, your 'another option' is the answer.

 

image.png

Thank you everyone. All worked out as needed :smiling_face_with_heart_eyes: