Count if two or more numbers appear in a set

agcereniv

New Member
Joined
Jan 12, 2014
Messages
4
Good evening. I have a large dataset in a table, each integer occupying its own column, and each row containing the full set of seven columns. Integers will not repeat in a row set (each is unique). I'm trying to identify when two or more integers appear together across a row set.
I'm using COUNTIF to identify the total counts for each integer, but do not know what function and syntax to use to count them together. I've tried combinations of COUNTIF, SUMPRODUCT, SUM and others but am clearly doing it wrong as I know that some do appear together, but I'm getting a 0 result with each try.

Help?
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

puru.sve

Active Member
Joined
Apr 29, 2010
Messages
292
Hi ,
You have to apply this formula to each row .. and it will give you the number of repentance.

=COUNTIF(A1:G1,A1:G1)-1 , paste and use ctrl+shift+enter

and then drag it.
 

agcereniv

New Member
Joined
Jan 12, 2014
Messages
4
Ah, the ctrl+shift+enter part I had forgotten.
I'll work on that, but there's one additional part. Currently, I have over 1,300 rows of data and the current request is to identify the frequency of the top 5-10 numbers that occur and how frequently they occur together. I'm fine doing additional derivative work, but what would you suggest I do?
Thank you for this critical first step - I will do it and continue to play with the data but wanted to get your additional input.
 

agcereniv

New Member
Joined
Jan 12, 2014
Messages
4
Here is a sample series with some of the numbers changed out.

3292345401516
30191226332322
42222749124133
36161815204122
13181420211536

<colgroup><col span="5"><col span="2"></colgroup><tbody>
</tbody>

In this case, what I'm trying to do is identify how many times the numbers 15 and 16 appear in each line. Puru.sve's solution should still apply but I don't know how to identify multiple criteria for the same range.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,413
Maybe something like this


A
B
C
D
E
F
G
H
I
1
Header1​
Header2​
Header3​
Header4​
Header5​
Header6​
Header7​
Result​
2
32​
9​
23​
45​
40​
15​
16​
2​
3
30​
19​
12​
26​
33​
23​
22​
4
42​
22​
27​
49​
12​
41​
33​
5
36​
16​
18​
15​
20​
41​
22​
6
13​
18​
14​
20​
21​
15​
36​

Formula in I2
=SUMPRODUCT(--(MMULT((($A$2:$G$6=15)+($A$2:$G$6=16)),ROW($A$1:$A$7)^0)=2))

Hope this helps

M.
 

agcereniv

New Member
Joined
Jan 12, 2014
Messages
4
Thank you, M. I've been away for a few days and am now able to try this. Presumably if I wanted to add additional numbers to the command, it would be by adding "+($A$2:$G$6=xx)" to the initial area, where xx is the next digit?
I will be giving this a try soon!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,137
Messages
5,599,943
Members
414,349
Latest member
new180885

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
Top