Dcount for finding Occurances in Column

Hey guys,

I have a problem. My column looks like this. Need to calculate the number of occurances of ASA or AFR etc.. in the column A.

I need to use Dcount and a criteria to get this solution

Basically these abbreviations are Short forms for World Regions(Continents).

Criteria is somewhat like this

RGN XRGN

ASA TRUE

A1: AFR,ASA,EUR
A2: ASA,ANT,NAM
A3: ASA,EUR,SAM,AUS
A4: PCF,AUS

Help me out , thanks

I have uploaded the worksheet, and following is the question.

Figure 5

H I
27 RGN XRGN
28 ASA TRUE
29 Region NoofOccurancs
30 9
31 ASA 9
32 AFR 9
33 AUS 7
34 EUR 5
35 NAM 5
36 SAM 7
37 ARC 2
38 ANT 7
39 ATL 5
40 INO 5
41 PCF 8


A data table is shown in Figure-5 above in the range H30:I41. This data table tabulates for each of the eleven regions the number of solar eclipses visible (out of the twenty four listed in the input). This table is obtained by data filtering using the criterion range I27:I28 and a DCOUNT formula. The column input cell is H28.

Question 3.1. What is the formula in cell I28?

Question 3.2. What is the formula in cell I30?

Please open attachment for further details.

H I
27 RGN XRGN
28 ASA TRUE
29 Region No ofOccurancs
30 BLANK 9
31 ASA 9
32 AFR 9
33 AUS 7
34 EUR 5
35 NAM 5
36 SAM 7
37 ARC 2
38 ANT 7
39 ATL 5
40 INO 5
41 PCF 8

The answers are given, above. next to each region , ASA , AFR etc, find the number of occurances in the entire column, refer to attachment.

Haha ! K , let me make this very simple for you

I have a column range. Now this column range consists for abbreviations of various continents and oceans. ASA - Asia , AFR - Africa etc.
The column Name is "visibility". (Basically where the eclipse will be seen".

Now, i have taken a simplified example in my first post.

All i need to do is, count the Number of times ASA, or AFR etc, occurs in the entire column range.

Ex.

A1: AFR , ASA , PFC
A2: ASA , ANT , ARC
A3: ASA , AFR

Now in this column A, i need to find how many time ASA occurs, but the catch is that each cell has many such Abbreiviations seperated by Commas.

For the above example , the answer is

Region No. of occurances
ASA 3(In cells a1,a2 and a3)
ANT 1(in cell a2)

And i need to use Dcount to do this, there may be simple ways , but the requirement is Dcount for the assignment, with a criteria. If you are still confused , open the attached Excel sheet, you will understand.

The actual problem statement i have posted before this. Hope this is clear enough.

yes this is an assigment, which consists of two problem statements, i have completely finished the first one, this is the 2nd one , which is almost complete. the last question states that i use DCOUNT with a criteria, which is whre i am stuck. I can think of other methods to do this, but Not able to complete with DCOUNT and criteria. please read the Word Document ( Questions 3.1 and 3.2).

Yea, this is the same impasse I am in. I know sumproduct will make this very easy, the only difference in my formula was the i used 1*isnumber instead of --isnumber . But since this is an assignment , i need to check all options. Can you refer this to someone else for a second look ? Thanks a lot anyways !!

Well , its an evaluative assignment. you cant ask the MASTER :D.