Advanced countif formula help - counting instances of common occurrences

T

trazer985

Board Regular
Joined
Jan 4, 2011
Messages
134
Hello excel savants,

A tough one for you.

So I have a table: [TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]
Letters are groups, numbers are events. I am trying to identify how many times you see the same events appear within the same groups, as a pair. I am representing the results in a matrix. I'll only fill in a few examples to get the idea. 1/3 (or) 3/1 appear within the same group 3 times, A,B and D. 1/2 ,2/1 appears twice, A and D.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Event[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]na[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]na[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]na[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]na[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]na[/TD]
[/TR]
</tbody>[/TABLE]


I would like a formula that can be dragged across and down, that will automatically generate how many times these pairings are seen. I'm usually ok at making formulae, but I just can't get my head around getting the same group right.

Thanks in advance

T
 
Sort by date Sort by votes
trazer985 said:
Hello excel savants,

A tough one for you.

So I have a table: [TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]6[/TD]
[/TR]
</tbody>[/TABLE]
Letters are groups, numbers are events. I am trying to identify how many times you see the same events appear within the same groups, as a pair. I am representing the results in a matrix. I'll only fill in a few examples to get the idea. 1/3 (or) 3/1 appear within the same group 3 times, A,B and D. 1/2 ,2/1 appears twice, A and D.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Event[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]na[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]na[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]na[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]na[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]na[/TD]
[/TR]
</tbody>[/TABLE]


I would like a formula that can be dragged across and down, that will automatically generate how many times these pairings are seen. I'm usually ok at making formulae, but I just can't get my head around getting the same group right.

Thanks in advance

T
Click to expand...

try this
Code: 
=COUNTIFS($A$1:$A$21,$G14,$B$1:$B$21,H$13)

my range starts in A1
 
Upvote 0
trazer985 said:
thanks for this, what's in cells G14 and H13?
Click to expand...

HAHA sorry. I copied you ranges from your post:

A 1
A 2
A 3
A 4
B 1
B 3
B 4
B 5
C 2
C 6
D 1
D 2
D 3
D 4
D 5
D 6
D 7
E 3
F 2
F 4
F 6

and pasted it into cell A1

I created a matrix that started with the Alpha list (vertically) in G14 and the Numerical List (horizontally) starting in H13.

So G14 is the first letter in the alpha list and H13 is the first number in the numerical list

***note both lists were created by copying the data from column A (alpha) or Column B (numerical) and removing the duplicates.
 
Last edited:
Upvote 0
RCBricker said:
HAHA sorry. I copied you ranges from your post:

A 1
A 2
A 3
A 4
B 1
B 3
B 4
B 5
C 2
C 6
D 1
D 2
D 3
D 4
D 5
D 6
D 7
E 3
F 2
F 4
F 6

and pasted it into cell A1

I created a matrix that started with the Alpha list (vertically) in G14 and the Numerical List (horizontally) starting in H13.

So G14 is the first letter in the alpha list and H13 is the first number in the numerical list

***note both lists were created by copying the data from column A (alpha) or Column B (numerical) and removing the duplicates.
Click to expand...

I think I didn't explain my point clearly, I'm trying to see the frequency where the numbers are paired together inside a group, not the letters, I don't care too much which groups they are inside.

I'm trying to find a way to pair them up. I'm not sure if arrays work with countif etc. This is where i'm astrugglin'
 
Upvote 0
Maybe something like this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][td="bgcolor: #DCE6F1"]
G​
[/td][td="bgcolor: #DCE6F1"]
H​
[/td][td="bgcolor: #DCE6F1"]
I​
[/td][td="bgcolor: #DCE6F1"]
J​
[/td][td="bgcolor: #DCE6F1"]
K​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
Group​
[/td][td]
Event​
[/td][td][/td][td="bgcolor:#DCE6F1"]
Event​
[/td][td="bgcolor:#D9D9D9"]
1​
[/td][td="bgcolor:#D9D9D9"]
2​
[/td][td="bgcolor:#D9D9D9"]
3​
[/td][td="bgcolor:#D9D9D9"]
4​
[/td][td="bgcolor:#D9D9D9"]
5​
[/td][td="bgcolor:#D9D9D9"]
6​
[/td][td="bgcolor:#D9D9D9"]
7​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
A​
[/td][td]
1​
[/td][td][/td][td="bgcolor:#D9D9D9"]
1​
[/td][td]
na​
[/td][td]
2​
[/td][td]
3​
[/td][td]
3​
[/td][td]
2​
[/td][td]
1​
[/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
A​
[/td][td]
2​
[/td][td][/td][td="bgcolor:#D9D9D9"]
2​
[/td][td]
2​
[/td][td]
na​
[/td][td]
2​
[/td][td]
3​
[/td][td]
1​
[/td][td]
3​
[/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
A​
[/td][td]
3​
[/td][td][/td][td="bgcolor:#D9D9D9"]
3​
[/td][td]
3​
[/td][td]
2​
[/td][td]
na​
[/td][td]
3​
[/td][td]
2​
[/td][td]
1​
[/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
A​
[/td][td]
4​
[/td][td][/td][td="bgcolor:#D9D9D9"]
4​
[/td][td]
3​
[/td][td]
3​
[/td][td]
3​
[/td][td]
na​
[/td][td]
2​
[/td][td]
2​
[/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
B​
[/td][td]
1​
[/td][td][/td][td="bgcolor:#D9D9D9"]
5​
[/td][td]
2​
[/td][td]
1​
[/td][td]
2​
[/td][td]
2​
[/td][td]
na​
[/td][td]
1​
[/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
B​
[/td][td]
3​
[/td][td][/td][td="bgcolor:#D9D9D9"]
6​
[/td][td]
1​
[/td][td]
3​
[/td][td]
1​
[/td][td]
2​
[/td][td]
1​
[/td][td]
na​
[/td][td]
1​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
B​
[/td][td]
4​
[/td][td][/td][td="bgcolor:#D9D9D9"]
7​
[/td][td]
1​
[/td][td]
1​
[/td][td]
1​
[/td][td]
1​
[/td][td]
1​
[/td][td]
1​
[/td][td]
na​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
B​
[/td][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td]
C​
[/td][td]
2​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11​
[/td][td]
C​
[/td][td]
6​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
12​
[/td][td]
D​
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
13​
[/td][td]
D​
[/td][td]
2​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
14​
[/td][td]
D​
[/td][td]
3​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
15​
[/td][td]
D​
[/td][td]
4​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
16​
[/td][td]
D​
[/td][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
17​
[/td][td]
D​
[/td][td]
6​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
18​
[/td][td]
D​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
19​
[/td][td]
E​
[/td][td]
3​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
20​
[/td][td]
F​
[/td][td]
2​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
21​
[/td][td]
F​
[/td][td]
4​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
22​
[/td][td]
F​
[/td][td]
6​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Array formula in E2 copied across and down
=IF($D2=E$1,"na",SUM(IF(FREQUENCY(IF(($B$2:$B$22=$D2)+($B$2:$B$22=E$1),MATCH($A$2:$A$22,$A$2:$A$22,0)),ROW($A$2:$A$22)-ROW($A$2)+1)=2,1)))

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.
 
Upvote 0
Marcelo,

I think we need something like this:

=IF($D2=E$1,"na",SUM(IF(FREQUENCY(IF($B$1:$B$21=E$1,IF(ISNUMBER(MATCH($A$1:$A$21,IF($B$1:$B$21=$D2,$A$1:$A$21),0)),MATCH($A$1:$A$21,$A$1:$A$21,0))),ROW($A$1:$A$21)-ROW($A$2)+1),1)))

which needs to be confirmed with control+shift+enter, not just with enter.
 
Upvote 0
Marcelo Branco said:
Maybe something like this



Array formula in E2 copied across and down
=IF($D2=E$1,"na",SUM(IF(FREQUENCY(IF(($B$2:$B$22=$D2)+($B$2:$B$22=E$1),MATCH($A$2:$A$22,$A$2:$A$22,0)),ROW($A$2:$A$22)-ROW($A$2)+1)=2,1)))

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.
Click to expand...

perfect, thank you!
 
Upvote 0
Aladin Akyurek said:
Marcelo,

I think we need something like this:

=IF($D2=E$1,"na",SUM(IF(FREQUENCY(IF($B$1:$B$21=E$1,IF(ISNUMBER(MATCH($A$1:$A$21,IF($B$1:$B$21=$D2,$A$1:$A$21),0)),MATCH($A$1:$A$21,$A$1:$A$21,0))),ROW($A$1:$A$21)-ROW($A$2)+1),1)))

which needs to be confirmed with control+shift+enter, not just with enter.
Click to expand...


Aladin,

Does my formula fail in some situations? Am i missing something?

I assumed:
No blank cells in column A
No duplicates in the same group, ie, this not gonna happen

[TABLE="class: grid"]
<tbody>[TR]
[TD]
Group​
[/TD]
[TD]
Event​
[/TD]
[/TR]
[TR]
[TD]
A​
[/TD]
[TD="bgcolor: #FF0000"]
1​
[/TD]
[/TR]
[TR]
[TD]
A​
[/TD]
[TD]
2​
[/TD]
[/TR]
[TR]
[TD]
A​
[/TD]
[TD="bgcolor: #FF0000"]
1​
[/TD]
[/TR]
[TR]
[TD]
A​
[/TD]
[TD]
4​
[/TD]
[/TR]
</tbody>[/TABLE]


M.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

GMC The Macro Man
  • Solved
Countifs with Multiple Criteria
Replies
3
Views
366
Kaper
Kaper
K
  • Solved
VLookup and Count formula
Replies
5
Views
120
etaf
etaf
T
  • Question Question
COUNTIFS with on Dates
Replies
6
Views
210
Alex Blakenburg
A
P
  • Question Question
Regextract formula to extract numbers
Replies
7
Views
221
Peter_SSs
Peter_SSs
J
  • Question Question
Novice question
Replies
2
Views
112
MARK858
MARK858

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top