Countif formula

[swdodd]

I am using the Countif formula to extract data from the first worksheet.


The cell contents will be either Assoc CTC, CTC or SR. CTC values and I am wanting to get a total of each one.


On the second worksheet, I am using the following formulas:


=COUNTIF(Results!F$3:F$70,"*" & "Assoc" & "*")

=COUNTIF(Results!F$3:F$70,"*" & "CTC" & "*")

=COUNTIF(Results!F$3:F$70,"*" & "SR." & "*")


Let's say there is 1 entry for each of these in the data base. When looking at the totals, I get 1 response for Assoc, 3 for CTC and 1 for Sr. The middle formula is picking up the CTC entry from the other two for a total of 3 where I nee it to show 1.


Is there a way to modify the middle formula to only pick up the CTC name and ignore the other two?


I would like to do this via Excel if possible. I don't know anything about VBA.


Thanks.


Steve

 

[Luke M]

We could just get rid of the wildcards since you know what the 3 values are going to be.

=COUNTIF(Results!F$3:F$70,"Assoc CTC")

=COUNTIF(Results!F$3:F$70,"CTC")

=COUNTIF(Results!F$3:F$70,"SR. CTC")


or at least remove the wildcards from the middle formula.

 

[NARAYANK991]

Hi Steve ,


Going by the definition of wildcards , the following formula ( your middle one ) :


=COUNTIF(Results!F$3:F$70,"*" & "CTC" & "*")


is supposed to match all three "Assoc CTC" , "CTC" and "SR. CTC" , mainly because of the following usage :


"*" & "CTC" & "*"


Removing the first "*" , and using only "CTC" & "*" , will ensure that "Assoc CTC" and "SR. CTC" are not counted.


Narayan

 

[swdodd]

Thank you so much !!!


Guess I was looking at it so intently that the obvious was missed. Tried the revision and it works perfectly.


You guys are the best !!


Have a great weekend.

 

[NARAYANK991]

Hi Steve ,


Thanks. You too.


Narayan