Counting with Array Formula

  • Thread starter Thread starter Werner Rohrmoser
  • Start date Start date
W

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
 
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
 
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
 
Hi Werner

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

Roger Govier
 
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
 
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
 
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
 
Back
Top