Tricky dynamic counting in Excel 1

[Scott24x7]

Hi All,
I've tried a few combinations to get this to work but I seem to be failing at it.

I have a series of rows that have data in them in the form of a single letter (R, I, D, W)

I have one row at the bottom of the list that also has the same values, in different orders (D, I, R, W)

So an example might look like this:

ROW1:   D D R I W W R
ENDROW: D R R I D D R

So the count for the end of Row1 should be 4 (First two D's match, then third R's, fourth I's, 7th' R's) so 4 matches. (Note each value is in its own cell, we could call Row 1 A1 and ENDROW A52).

How can I count (using COUNTIF, or COUNTIFS or some other clever method?) the matches for each row. I give one row as a simple example, and I can replicated it on the other rows.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[1726]

Use an Array Formula:

=SUM(B1:H1=B52:H52,1,0)

To enter an array formula press Shift+Ctrl+Enter in place of the normal Enter key

Formula bar will show {=SUM(B1:H1=B52:H52,1,0)}
- typing the brackets will not work

Array formulas allow you to perform multiple calculations - i.e. compare corresponding pairs of cells within the ranges

Cheers

 

[1726]

Oops - major typo in formula!!

Try SUM(IF(B1:H1=B52:H52,1,0))

Sorry!

 

[combo]

Alternative:
=SUMPRODUCT(--(B1:H1=B$52:H$52))

combo

 

[Scott24x7]

Combo, awesome, worked like a charm!

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[ScottScott3]

https://www.ablebits.com/office-addins-blog/2015/02/11/excel-offset-function/

 

[SkipVought]

@ScottScott3, posting a link with no explanation is not the way that Tek-Tips works. The OFFSET() function may have some relevance, but you would have to put that function and whatever example might be in the link, in context for the OP.

Why not take a few days and spend some time checking out how things work here at Tek-Tips. This is not your average tek forum.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Scott24x7]

Oh, one question about understanding @combo's solution, what is the "--" leading the second set of parenthesis? That part kind of baffles me.


Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[Deniall]

The "--" ensures the following expression will be treated numerically.[ ] (The two negation operations "cancel each other out" in effect.)[ ] It certainly looks odd, but to some extent that is actually an advantage.