Counting with multiple criteria and different tables

Aguadiablo

New Member
Joined
Nov 29, 2013
Messages
13
I have many different tables all on one worksheet, with identical data in appearing in more than one table, but never more than once per table.

It looks something like this:

Table 1 Name | Data 1 | Data 2 | Data 3 | Data 4
Table 2 Name | Data 2 | Data 4
Table 3 Name | Data 3 | Data 4
Table 4 Name | Data 2 | Data 3 | Data 4

Is there a formula that can give the me total number of times that both Data 3 and Data 4 appear together in all the tables?

I.e. Data 3 and Data 4 both appear in Table 1, 3, & 4 so the formula should give me the number 3.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
try
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

not sure what ranges your tables are in the same worksheet - do you mean worksheet and not different tabs in a workbook ?

did you actually mean the text data 3 - or could this be any value

use "" for text
 
Upvote 0
Well here's a sample from my worksheet in order to clear things up.
AIron ManHawkeyeColossus
BIron ManHawkeye
CIron ManColossus
DHawkeyeColossus

<tbody>
</tbody>

I want to be able to find how many times Iron Man is paired up with Hawkeye, which in this case is 2.

I've entered into a cell the formula =COUNTIFS(B1:C4,"Iron Man",B1:C4,"Hawkeye"). However that gives me a result of 0.
 
Upvote 0
try
=COUNTIFS(B1:B4,"Iron Man",C1:C4,"Hawkeye")
 
Upvote 0
That works for the small sample and those two examples. But it doesn't work with the full data and every other combination. For example if I wanted to do Iron Man and Colossus. Or if I was to add the following row

E
Beast
Iron Man
Hawkeye

<tbody>
</tbody>
 
Upvote 0
But it doesn't work with the full data
as i mentioned
not sure what ranges your tables are in the same worksheet - do you mean worksheet and not different tabs in a workbook ?
hows you data setup ??

how many columns are you likely to fill - ?
 
Upvote 0
and how are the three tables set out

1 table could be 30 columns and are you counting all columns ?

i think we need a full spec of how your data is structured and how you want to count
 
Upvote 0
I have rearranged my data to place it into 1 table that has 30 columns and 140 rows. However, over time new data will be added into the table and will likely increase both number of columns and rows.

As for the counting, I want to match any of the names, that I choose in a combination, per row. E.g. I choose Iron Man and Colossus, it will then look at the first line to see if both names are present, if they are it counts as one, and then continues down the table checking for the other times both names appear in a single row.

If it all possible it would be useful for it to ignore some some combinations on some rows as well. For example it would only count Iron Man and Hawkeye or Iron Man and Colossus but not Hawkeye and Colossus in row A, but in other rows it would still count Hawkeye and Colossus.
 
Upvote 0
I have rearranged my data to place it into 1 table that has 30 columns and 140 rows. However, over time new data will be added into the table and will likely increase both number of columns and rows.

As for the counting, I want to match any of the names, that I choose in a combination, per row. E.g. I choose Iron Man and Colossus, it will then look at the first line to see if both names are present, if they are it counts as one, and then continues down the table checking for the other times both names appear in a single row.

If it all possible it would be useful for it to ignore some some combinations on some rows as well. For example it would only count Iron Man and Hawkeye or Iron Man and Colossus but not Hawkeye and Colossus in row A, but in other rows it would still count Hawkeye and Colossus.

Would you post 5 rows and 5 columns of data as you now have it?
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

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