count formula _1

Frank .S
Frank .S used Ask the Experts™
on
hi experts, can you help me with a formula for the following;

                  ESTIM
    AB          y
    AC          n
    SC          y
    JM          n
   total       x 2

to count/sum 1 for each cell value with a "y", and to display total as x 2, so the formula needs to be in the "x 2" cell
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Assuming the 4 cells to count are in cell B2:B5, you can use this formula...

=COUNTIF(B2:B5, "y")
Frank .SBuilding Estimator

Author

Commented:
hi expert, I know you have already answered this question but this formula no longer works now that I have added letters to the numeric value, ie
10 (s4)
20 (s1)
40 (d2)
the result here should still = 3 because there are 3 values in this column, but with your formula above it no longer works? is it because of the value in brackets? the bracket value is in the same cell as the numeric value.
You'll need to post an example workbook so I can see what's going on.
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Rob HensonFinance Analyst

Commented:
As mentioned in your previous question, there are numerous COUNT functions. To count cells that include text you have to use COUNTA which counts cells that are not empty, assuming range suggested by Wayne:

=COUNTA(B2:B5)

Thanks
Rob
Top Expert 2014

Commented:
@Frank

You went from "count the 'y' instances" to something completely different.  Please post a comment that describes your problem completely.
Rob HensonFinance Analyst

Commented:
I think the comment above (42388092) was meant for previous question (if statement) as that was referring to counting numeric data (dates to be more precise).
Frank .SBuilding Estimator

Author

Commented:
hi experts, sorry for the confusion, what i wanted was to count the cell range values other than "y" so for each value other than "y" the sum is to add 1.
=COUNTIF(B2:B5, "<>y")
Frank .SBuilding Estimator

Author

Commented:
hi wayne if i wanted to count everything other than "y" and say "z" would the formula be =COUNTIF(B2:B5, "<>y,z")
Rob HensonFinance Analyst

Commented:
As per previous question, use COUNTIFS for multiple criteria.

=COUNTIFS (B2:B5,"<>Y",B2:B5,"<>z")

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial