using a wildcard in a formula

[smiler44]

From a previous question I posted I ended up with formula
=SUMPRODUCT((C1:C65535="jon")*(F1:F65535 = "good"))

I now need to go a step further and use a wildcard on jon so jon, jonc, jont will be counted when good is found in the corresponding column.

My attempts have all failed.
Can a wildcard be used?

smiler44

 

[DanWall]

Again, you may be best off doing this in an 'evaluation' column.
It makes life a lot more simple.

It would look like this:
=IF(AND(COUNTIF(C1:C65535,"*jon*")=1,F1:F65535="good"),1,0)
Then at the top of the column (or anywhere else)
Use a =SUM(EvaluationColumn) to count the cells returning 1.

TIP: Don't use all 65535 rows unless you absolutely need to.
Even 2000 or 3000 is enough for most application.

 

[Brianwarnock]

The document I attached to your other thread shows you how to achieve this using Find or Search.

Brian

 

[smiler44]

Brian,
sorry I did not see your reply.
Someone at work came up with :- =SUMPRODUCT((LEFT(C1:C10,3)="jon")*(F1:F10 = "good"))

It does not use a wildcard but uses the first 3 letters found in C1:C10 and if they are JFC counts the number of good on the same row.
Smiler44

 

[Brianwarnock]

Never tried that it looks as good if not better if you are working with the starting characters.

Brian

 

[smiler44]

Brian,
thank you for the word ducument i have just printed it off and I think it has given me the answer to another posting I just entered onto this forum about adding to a formula. Even if I have not craked it with the shorest formula I at least have a formula that gives me what I want.
Thank you
smiler44