Counting with Array Formula

[Werner Rohrmoser]

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

 

[Rowan]

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]

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

 

[Rowan]

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

Regards
Rowan

 

[Roger Govier]

Hi Werner

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

Roger Govier

 

[KL]

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]

Hi KL,

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

Werner

 

[Biff]

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]

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