How do create a matrix formula to calculate in all range (B:F) with this rare condition.

[dpparner]

Bom dia família,

Preciso criar uma fórmula matricial para calcular quantas vezes essa coincidência aparece em todo o intervalo  B:F , de acordo com essas condições ( A) na primeira linha ), ( M) na segunda linha ). Tentei com essas 2 fórmulas, mas conta apenas uma coincidência. Alguém tem alguma sugestão por favor?

Desde já, obrigado.

[#2]

@dpparner 

=SUMPRODUCT(COUNTIF(OFFSET(B3:F3,ROW(1:20)-1,0),"A")*COUNTIF(OFFSET(B4:F4,ROW(1:20)-1,0),"M"))

Maybe with this formula.

[#3]

Thank so much my hero, Im going to test the formula and give u a feedback.

[#4]

@OliverScheurich 

The formula calculates perfectly in each case separately, just for the second analysis, the range changes and in that case I don't know how to fix it. I'm trying to send you the original excel but the page doesnt allow or I dont know how to attach it here to show you. I think that i didnt explaing my self as well as i should, sorry is my first time. Cell I5(350) will be dynamic and will take all the values of column (I), column (J) will be the same. Each element(Freq) of column (I), will be tested individually with all element of column (J), and will show the result in the adjacent cell (K). It is an study of frequency in 5 different devices. That's why I need the range be the same, also because it will continue to increase during two years.

If you know the way to attach an excel here, i'll send you..

[#5]

I cant send the picture as an answer neither, this site doesnt make the job easier for first users.
Im trying to find the way to send you.

[#6 · Accepted solution]

@dpparner 

I recommend to post your question in the Microsoft Community for Excel. There are many more users than in the Microsoft Community for Data where your post is right now.

When you start a new discussion you can drag and drop or browse file as shown in the attached file.

[#7]

Sorry, im cracy here trying to understand how the sites work, but is complicated to find the rights answers to do it. I dont know if Im in the comunity or not, or where am i inside the site, let me study the site to doing that correctly .

[#8]

Thanks, only now I understood that there is an internal structure by themes, thanks for suggesting, didn't know it, I just published and it came out there. But any way, help me with this please, I have been looking for a solution in several excel groups for several months and nobody has given a solution closer to yours. This formula is perfect, only remains to lock the range.

=SOMARPRODUTO(CONT.SE(DESLOC(C17:G17;LIN(17:84)-1;0);$I$5)*CONT.SE(DESLOC(C18:G18;LIN(17:84)-1;0);J17))

[#9]

@dpparner 

In the attached file there are two sheets. In sheet "Tabelle2" is your formula.

=SOMARPRODUTO(CONT.SE(DESLOC(C17:G17;LIN(17:84)-1;0);$I$5)*CONT.SE(DESLOC(C18:G18;LIN(17:84)-1;0);J17))

Is this what you want to do?

[#10]

Im going to check right now dear.
I just posted in the Excel community like you suggested jajjajaa. Thanks, you are amazing i learned something more with u today.

[#11]

I already checked the formula u send, and i dont have words to thank you for this, is perfect. You cant imagine how important it is for me. From this moment I will always be in debt with you.

God bless you.