How can count the number of cells that fulfill certain criteria in other cells

[CS Chia]

Below is my dataset:

01-jan 10-jan 25-jan 4-feb 22-feb 11-mar
X X X X

The requirement is to determine how many X are there for each month.
The number of dates for each month is variable.

The second requirement is to determine the percentage of X occurrences.

Expected result is as follows:
Jan Feb Mar
2 1 1
2/3 1/2 1/1

is there any formula I can use to do the count?
The assumption is that the year is the same, i.e. 2013
or moving forward, 2014, etc...

Many thanks in advance for the help

 

[Claus Busch]

Hi,

Am Fri, 16 Aug 2013 09:47:00 +0100 schrieb CS Chia:

Below is my dataset:

01-jan 10-jan 25-jan 4-feb 22-feb 11-mar
X X X X

Expected result is as follows:
Jan Feb Mar
2 1 1
2/3 1/2 1/1

your dataset in A1:Z2
The month names as text in A5:C5
Then try in A6:
=SUMPRODUCT(--(TEXT($A$1:$Z$1,"MMM")=A5),--($A$1:$Z$1>0),--($A$2:$Z$2="X"))
and in A7:
=SUMPRODUCT(--(TEXT($A$1:$Z$1,"MMM")=A5),--($A$1:$Z$1>0),--($A$2:$Z$2="X"))&"/"&SUMPRODUCT(--(TEXT($A$1:$Z$1,"MMM")=A5),--($A$1:$Z$1>0))
and copy both formulas to the right


Regards
Claus B.

 

[CS Chia]

Hi Claus B.,

Thank you so much... that is sooo cool.
It works for me...

Really appreciate your help on this.

cheers,
CS Chi