Counting data in 2 dimentional tables

[nir.ben]

Hello,

I have an excel file that contains 2 sheets:
Sheet1: Contains a summary of Titles and Modes. There is a variant amount of titles and a fixed amount of modes - 2.
Sheet2: Contains the data to be counted: 1 Column contains titles (corresponding to the list of titles in sheet1); 1 column contains some other type of data which is irrelevant; 1 column contains modes (corresponding to the 2 modes in sheet 1).

What I want to do is this:
1. Scan for a specific title in sheet2, and count the amount of its occurances with Mode 1.
1.1 Display the amount in sheet1.
2. Scan again and count the amount of occurances with Mode2.
2.1 Display the amount in sheet1.
3. Do the same for all titles.
4. At any given time, a title can be added or subtracted from the sheets. Sheet1 can be used as an anchor for retrieving all titles.

I have attached an example XLS that shows the above.

My questions:
1. Can this be done with native excel functions?
2. If so, how?
3. If not, what is the most efficient way of implementation using VBA?

Thanks a lot.

[shrivallabha]

Welcome to VBAX!

1.
You can use following formula in column C:

=SUMPRODUCT((Data!$B$3:$B$11=$B2)*(Data!$D$3:$D$11=$C$1))

And in column D it will change only slightly to get Mode 2

=SUMPRODUCT((Data!$B$3:$B$11=$B2)*(Data!$D$3:$D$11=$D$1))

2.
This you can achieve also by using VBA.

3.
You may also have a look at Pivot Tables. They are equally effective.

4.
The above formula is courtesy of sub-forum where it is well-explained by XLD.

http://www.vbaexpress.com/forum/forumdisplay.php?f=98

[nir.ben]

Thank you shrivallabha! Both for providing with a simple solution, and referring me to places where I can learn more on the subject.

You've been amazingly helpful.

Thanks again!
Nir