Specific Countif formula (matching cell then counting all occurrences in column)

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
915
Office Version
  1. 365
Platform
  1. Windows
Hello,

I am looking for a specific countif formula. Basically in column E I have names of locations:

E

Blue
Red
Blue
Green

In Cell H10 I have the word "Blue"
In Cell H9 I want a formula to look in Column E, find how many match Cell H10 and return the number, in the above case: 2

I thought it would be something like: COUNTIF(E:E,H10,E:E) but I know this is not right and I am missing something. I also thought maybe:
COUNT(IF(E:E=H10),E:E)) but I know this is incorrect as well. I am drawing a blank at the moment and hope someone can help me.

Thank you

Carla
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
I now see how it can be related. Is there a way to not count blank cells?
 
Last edited:
Upvote 0
I have this formula but it gives the result of "0" instead of a blank if the reference cell (H10) is blank.

=COUNTIFS(E:E,H10,E:E,"<>")
 
Upvote 0
If H10 has a formula that sometimes returns "", use
=If(H10="","",countif(E:E,H10))
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top