Summarizing Spreadsheet Data

[County Engineer]

I am not sure if this is the correct sub-forum or not but this is my problem.
I have a rather large workbook with many worksheets and am trying to summarize the data in 1 column which is common to all of the worksheets within the workbook. For example my column D has approximately 25 different codes which are a combination of letters and numbers. My goal is to have a summary total for each code for all of the worksheets combined. I would think that this is a common function of an Excel worksheet but I have never performed this operation before.

[cheal2]

You could do a sumif function on a Summary Sheet that sums all occurrences of each code on each sheet. Something to the effect of the below formula where A2 is equal to a specific code and you'd put this equation in B2. For N sheets.

Please Login or Register  to view this content.

Hope this helps.

[County Engineer]

Thanks for your help but I still need some assistance and clarification on the formula. I am trying to determine how many times the code R1-1 is entered in column D on all of my individual worksheets. I see that you use no cell refrences in your formula, why is that? I am also confused about the sheet1 !A:A reference in the formula. I attempted a formula on one of my worksheets but it didn't produce the results that I was looking for.

here is my formula that is obviously erroneous since it didn't work: =SUMIF(D6:D141,R1-1)

[Lemice]

Hello,

For your question regarding no cell reference, in the formula cheal2 posted, he used D:D for example, it refers to every single cell in column D in the entire worksheet. Similarly, 'Sheet 1 '!A:A means the entire column A in Sheet 1.
For your intention to find how many time a code has been used in your column, I am not sure that R1-1 is a Cell value, which can be counted through COUNTIF and similar formula, or you are trying to count it in others formula.

Hope this help.

[County Engineer]

Thanks Lemice. Do you have any suggestions on how to solve my problem? I am also confused by cheal2's use of the A:A rnage in the formula when I am only trying to get a total of the code usage in column D. Is there another formula that I could use or refer to a cell value that corelates with my codes to summarize my data?

[ChemistB]

How many different sheets are we talking about? With R1-1 in A2 of a Summary sheet

=COUNTIF(Sheet2!D:D,A2) +COUNTIF(Sheet3!D:D, A2) + COUNTIF(Sheet4!D:D, A2)
If there are a lot of sheets, a macro of UDF might serve you better.

[Lemice]

I am starting to get what you are trying to do.

Let's say you have a list of 25 unique codes in another sheet, and you wish to find the sum of every data for each code, say code "R1-1" in column A in sheet 1, it should be something like this:
=SUMPRODUCT(--('Sheet 1'!A:A="R1-1"),B:B)
What this function does is that everytime it encounters code "R1-1" in column A in Sheet 1, it will add the corresponding value in column B (For example, in row 120 on column A, cell A120 = "R1-1", then it will add the value in cell B120, same row) and shows you the sum of it.
If you want to count how many time code "R1-1" has shown up in a sheet, try
=COUNTIF('Sheet 1'!A:A,"R1-1")
It's basically the same as your formula, with the exception of "R1-1", since R1-1 is a text.

Let me know if this is the type of result you are looking for.

[cheal2]

Assuming you just want to count the number of occurrences then yes a COUNTIF statement will be more useful.

List your unique codes in Column A. Then put the equation in Column B.

To clear up about the meaning of the equation... D:D is a new way of grabbing all contents of a column instead of doing D2:D100 and then finding out as you add more data that you don't need to update your formula. Secondarily, there is a cell reference to A2 which is the code you are looking for in a particular sheet and column.

=COUNTIF(Sheet2!D:D,A2)