Filter/search and count.

[edsashley]

morning,

I have been using a formula for a while now which works fine. =COUNTIFS(F:F,”Dave*”,H:H,”*”). This will count the cells in column F that have Dave in as long as column H is also filled. I have to do this for different names.

I am trying to streamline this by inputting names into a drop down list in cell ‘O9’ and using whatever is selected as my variable.

I have tried amending the formula to =COUNTIFS(F:F,”=O9*”,H:H,”*”) but this does not work.

Can anyone offer any suggestions please.

 

[Sergius]

=COUNTIFS(F:F,O9,H:H,”*”)

 

[edsashley]

=COUNTIFS(F:F,O9,H:H,”*”)

This didn’t work.

 

[Peter_SSs]

Just noting that all formula suggested above will need to have straight quote marks " not sloping ones ”
I also do not know what your names might be but if counting for "Fred" you would also get rows with "Frederick", "Freda" etc. Is that a possible problem?

 

[Peter_SSs]

This didn’t work.

See the first line of my last post.

 

[edsashley]

That is not a problem but I thought that was what the asterisk was for to make sure it was definitive ie “Fred*” would not result in Frederick.

 

[edsashley]

See the first line of my last post.

They are straight quote marks it’s just I typed this on the phone.

 

[edsashley]

Here is my sheet and the formula in K6

 

[Peter_SSs]

Here is my sheet and the formula in K6

From that image, how would we know know what is K6 or O9 or column F or column H?

I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be. We can also copy from it for testing.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[edsashley]

From that image, how would we know know what is K6 or O9 or column F or column H?

I suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be. We can also copy from it for testing.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Apologies. I have only just realised this. Attached is a better screenshot. The search criteria table populates the drop down located at ‘O9’ highlighted green. Whichever is selected from here is what needs to be in the formula of K6.

Thanks for any help.