Formula to count number of 3's and 4's that appear in several different cells.

Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.

If you need to post an image post it in the *.jpg format.

Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.

This will count the number of 3s in a single cell. However, |I'm really not sure wht your full requirement is. Can you explain again and let us see a worksheet, not a picture of one...

Will you please attach a sample Excel workbook? Please don't attach a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

I forgot to add the formula....

=LEN(A1)-LEN(SUBSTITUTE(A1,"3",""))

I can't get the damn thing downloaded. Use this formula:

SUMPRODUCT(LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,{"3","4"},"")))

change A1:A5 to suit your needs.

Did you try my formula on your own sheet?

What is in the cells in between? If they are empty... it'll still work.

Im away from my pc. Wxactly which cells do you want "counted"?

Any cell next to a cell that contains "pre-assessment". They are all in column C. It is like every 3rd cell.

So you see column B:
Pre-Assessment
Post-Assessment
Black Cel
Repeats.

If I understand correctly then this formula should count the 3's and 4's in cells in column C corresponding to cells in column B that display the term "Pre-Assess":

Formula:

Please Login or Register  to view this content.

Let me know if you have any questions.

OK. This works for me in the Excel sheet attached:

=SUMPRODUCT(($A$1:$A$19="a")*(LEN($B$1:$B$19)-LEN(SUBSTITUTE($B$1:$B$19,{"3","4"},""))))

Can you adapt it for your own sheet?

Great! I'm glad to have helped! If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.