Using a countif function for text string data.

Hi everyone.

I'm currently working on a spreadsheet (see attached) which will have both numeric and text string data. This will be sorted by calender month and linked to another spreadsheet.

Could someone please list 2 functions for me.

The 1st would allow act like a filter allowing only data to be counted for a selected month from a drop down list.

The 2nd allows a counting of pre-set (drop down list) text strings to be counted.

Sorry seems a bit complicated and really I should have either a created a pivot report or used access but I'd like to continue with Excel for this exercise.

Thanks in advance.

I'm so sorry it sounds very confusing.

If you look at the spreadsheet, row D7 to D16 are the questions I need answered from the data entered below it.

I managed to get the countif function done to count how many clients were on for a month selceted from the drop down list in cell D5.

What I now need to do is add a similar function where I can change the logical test so that it counts text strings (which are selected from a drop down list within the spreadsheet) for cells D8, D10, D12, D15 & D16.

The trouble I have is that there are numerous text strings click on cell L20 for example. Also to add to the complications one of the drop down entires may need the data inputer to add extra text into cell M20......

AAAAAAAAGGGGGGGHHHHHHHH Its not going to work, I just know it......

{=COUNT(IF(B$20:B507=D$6,1,""))}

The above is my basic formula for the array yet where can I add another function within the above to count text strings etc....

I think your right VBA Noob... I'm asking too much...

Originally Posted by VBA Noob

What's your expected results ??

A total of Unquie items or list all 30 answer in one cell with a count ??

e.g for D9 would you have something like



VBA Noob

What I'd like to set up is a function like below whereby the first part 'COUNT(IF(B$20:B507=D$6,1,"")' will set the filter so that the only months selected from the drop down list will be counted. The second part of the function would then be used to count the different areas I need to get (cells C8-C16).

For example to count all males within the selected month would work out as;

{=COUNT(IF(B$20:B507=D$6,1,"")*AND(SUM($K$20:$K$508="Male")))} - This does'nt work but used as an example.

(Thanks for your help by the way)....

A total of Unquie items. This could then be linked into another spreadsheet.

VBA Noob........ you are fantastic. I'm going to try these now and get back to you with the results!

Thanks very much.