Thread: Counting Data From Multiple Sheets

Hello,

I was wondering how to go about this...

I have 5 columns of numbers located in columns T-X in each sheet that have been sorted and counted.

I want to then, on the final sheet, count how many times each number occurred in ALL of the sheets for that specific column, and how many times each number occurred overall between the 5 columns.

On the final sheet, column 1 would be referring to column T, column 2 would be referring to column U, etc..

Also, if that number doesn't exist in a column or even at all, I would like to have a 0 placed in the cell on the final sheet. Could this be accomplished by setting a range of numbers before it begins counting?

Any form of guidance would be greatly appreciated.

I am using Microsoft Excel 2007, and I have attached an example document to help provide a visual of what I am trying to achieve.

There are currently only three sheets in my example document. The first two contain the numbers and the third sheet would be the sheet to total them in.

Thanks in advance,

w3dgie

no workbook attached!

You could use a "3D COUNTIF" based on H:L on each sheet.

Using your sample file, first create a list of the sheets to be included in the formula, eg:

Sheet3!I1 & I2:

Sheet1
Sheet2

Then:

Sheet3!B2:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$I$1:$I$2&"'!R1C[6]:R100C[6]",FALSE),$A2))
copy across matrix B2:F33

Column G is a simple Sum of B:F obviously

The cell references to be used are included in my prior post, specifically:

Sheets to be listed in I1, I2

Formula to be placed in B2 and then applied to remainder of results matrix (B2:F33)

Originally Posted by w3dgie

Sometimes I will have workbooks with less than 5 columns and sometimes more than 5 columns, will this formula work the same, whether it's more or less than 5 columns?

If in doubt... test

Pending location of data yes it should be fine - it uses relative referencing within the INDIRECT such that when dragged to the right it references the next column and so on and so forth.
If the first column of source data moves in relation to the first column of results then you will need to adjust the # in the C[#] offset
we use 6 in the example as first column of results is B and first column of source data is 8 so 8-2 = 6

For a brief overview of R1C1 notation (as used in the INDIRECT) see: http://www.excelforum.com/2171545-post2.html

Okay thank you for your quick reply.

Could you explain that in terms that I would better understand? I'm not really following you right now. I understand that the data is starting in column B, but I do not understand what you mean by the 8-2=6 thing.

I read your article, I am not following it. Very confusing.

So let's say I had 3 columns. Would it then be, "!R1C[4]:R100C[4]" ??

!R1C[4]:R100C[4] seems to be producing in accurate results.

I found the problem. It was only adding 100 rows. It is accurate now. Thanks a bunch for all of your help. Keep up the good work!

I can't really explain R1C1 any more simply than the referenced link I'm afraid.

If you're finding it hard to follow I'd suggesting Googling Excel R1C1 for more material - some of which is bound to make more sense to you.