Hello,

below you can see a table devided in months and weeks.
In every week there can occur an event, here called "A".

Question:
How can I count in how many months an event happens.
Result for table below is 2, in Jan and in Mar.

Jan Jan Jan Jan Feb Feb Feb Feb Mar Mar Mar Mar
wk1 wk2 wk3 wk4 wk5 wk6 wk7 wk8 wk9 wk10 wk11 wk12
A A A A

Thanks for your support.

Regards
Werner

Enter Jan in A6, Feb in A7 etc:
In B6 enter
=SUMPRODUCT(--($A$1:$AV$1=A6),--($A$3:$AV$3="A"))
and copy down.

Hope this helps
Rowan

Werner Rohrmoser wrote:
Hello,

below you can see a table devided in months and weeks.
In every week there can occur an event, here called "A".

Question:
How can I count in how many months an event happens.
Result for table below is 2, in Jan and in Mar.

Jan Jan Jan Jan Feb Feb Feb Feb Mar Mar Mar Mar
wk1 wk2 wk3 wk4 wk5 wk6 wk7 wk8 wk9 wk10 wk11 wk12
A A A A

Thanks for your support.

Regards
Werner

Thanks for your answer,
but I'd like to know the number of months, where I have an "A" and not
the number of weeks.

Werner

Now I see. That I'm not sure of. I'll get back to you if I come up with
something.

Regards
Rowan

Werner Rohrmoser wrote:
Thanks for your answer,
but I'd like to know the number of months, where I have an "A" and not
the number of weeks.

Werner

Hi Werner

Could you not use Rowan's suggestion, then in another cell
=COUNTIF(B6:B11,"0")
Regards

Roger Govier


Werner Rohrmoser wrote:
Thanks for your answer,
but I'd like to know the number of months, where I have an "A" and not
the number of weeks.

Werner

try this ARRAY formula (confirm with Ctrl+Shift+Enter, not jut Enter):

=SUM(--ISNUMBER(MATCH({"Jan","Feb","Mar","Apr","May","Jun ","Jul","Aug","Sep","Oct","Nov","Dec"},IF(A3:L3="A ",$A$1:$L$1),0)))

Regards,
KL


"Werner Rohrmoser" wrote in message
oups.com...
Thanks for your answer,
but I'd like to know the number of months, where I have an "A" and not
the number of weeks.

Werner

Hi KL,

thank you very much, that's exactly what I need in order to solve my
task.

Werner

Hi!

Array entered:

=SUM(N(FREQUENCY(IF(A3:L3="A",MATCH(A1:L1,A1:L1,0) ),MATCH(A1:L1,A1:L1,0))0))

Biff

"Werner Rohrmoser" wrote in message
oups.com...
Hello,

below you can see a table devided in months and weeks.
In every week there can occur an event, here called "A".

Question:
How can I count in how many months an event happens.
Result for table below is 2, in Jan and in Mar.

Jan Jan Jan Jan Feb Feb Feb Feb Mar Mar Mar Mar
wk1 wk2 wk3 wk4 wk5 wk6 wk7 wk8 wk9 wk10 wk11 wk12
A A A A

Thanks for your support.

Regards
Werner

Hi Biff,

thanks a lot, that's a formula which can be used generally
without using an array constant like {"Jan", "Feb", etc.}
Great!

Werner