using search criteria with the IF function

[mmansur04]

So I have this function:

=SUM(IF(FREQUENCY(IF(A$7:A$45="Macys",MATCH(F$7:F$45,F$7:F$45,0)),ROW(A$7:A$45)-ROW(A$7)+1),1))

What this function does, is looks at a list in column A and counts the number of times "macys" appears, but will only count it once for every time "macys" coordinating job number is repeated.

before I arrived at this equation I was using:

=COUNTIF(A7:A45, "macy*")
this function basically did the same thing as the top function, except check for matching job numbers. What I love about this function is that it counts the number of times "macy" pops up, even if it is spelled "macys" or "macy's" or something like "macys minneapolis" all because I have the * at the end of the word.

What I need help with is being able to incorporate that same concept into the first formula so the word doesn't have to match exactly. ive tried using "macy*" in the first formula, but it just returns the value to 0 because it is searching for the EXACT same criteria.

thanks!

 

[ghrain22]

don't think that wildcard will work with that. You'd probably need a different formula. I'm not sure what you're trying to accomplish in conjunction with the coordinating job number. But you may try a countifs instead of countif.
COUNTIFS function - Excel

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(SEARCH("Macys",A$7:A$45)),
  IF(1-(F$7:F$45=""),MATCH(F$7:F$45,F$7:F$45,0))),ROW(A$7:A$45)-ROW(A$7)+1),1))