I have a workbook with 10 different worksheets. Each worksheet has 3 columns
of data with varying number of rows.

Example:

Worksheet 1
Col A Col B Col C
1 3 TextA
7 0 TextB
1 0 TextE

Worksheet 2
Col A Col B Col C
1 0 TextA
1 0 TextB
7 1 TextC
2 2 TextE
1 0 TextF

Worksheet 3
Col A Col B Col C
3 2 TextG
5 5 TextN

etc...

Columns A and B are counts of the description in Column C.

Column C will only be present if there is a positive value in Col A.

I want to have a summary sheet that will have sum of the counts in Column A
and Column B that correspond with the definition in Column C

Summary
Col A Col B Col C
2 3 TextA
8 0 TextB
7 1 TextC
3 2 TextE
1 0 TextF
3 2 TextG
5 5 TextN

I believe I need to use a LOOKUP function, but have been unsuccessful. I
can change the output so Column C is actually the first column and then have
the counts follow (figuring that may help me use the VLOOKUP function).

I've put all of the worksheet names in cells and tried referencing them by
name (also like the trick I read where you have a Start and End worksheet and
hide them) as to be able to easily reference all the sheets, but I think I'm
making things too difficult.

Any help/guidance would be greatly appreciated.

Hi,

Most Excel functions don't handle 3-D references, so your best solution
would probably be, in cell B1:

=COUNTIF(Sheet2!B$1:$C$100,C1)+COUNTIF(Sheet3!B$1: $C$100,C1)+COUNTIF(Sheet3!B$1:$C$100,$C1)

Where C1 is something like TextA. The way I've written it you can copy it
down and over to the left and it will do all of your work.

If this helps please, please click the Yes button

Cheers,
Shane Devenshire

"Big John" wrote:

I have a workbook with 10 different worksheets. Each worksheet has 3 columns
of data with varying number of rows.

Example:

Worksheet 1
Col A Col B Col C
1 3 TextA
7 0 TextB
1 0 TextE

Worksheet 2
Col A Col B Col C
1 0 TextA
1 0 TextB
7 1 TextC
2 2 TextE
1 0 TextF

Worksheet 3
Col A Col B Col C
3 2 TextG
5 5 TextN

etc...

Columns A and B are counts of the description in Column C.

Column C will only be present if there is a positive value in Col A.

I want to have a summary sheet that will have sum of the counts in Column A
and Column B that correspond with the definition in Column C

Summary
Col A Col B Col C
2 3 TextA
8 0 TextB
7 1 TextC
3 2 TextE
1 0 TextF
3 2 TextG
5 5 TextN

I believe I need to use a LOOKUP function, but have been unsuccessful. I
can change the output so Column C is actually the first column and then have
the counts follow (figuring that may help me use the VLOOKUP function).

I've put all of the worksheet names in cells and tried referencing them by
name (also like the trick I read where you have a Start and End worksheet and
hide them) as to be able to easily reference all the sheets, but I think I'm
making things too difficult.

Any help/guidance would be greatly appreciated.

In Summary sheet:

Criteria start in C1

In A1:
=SUM(SUMIF(INDIRECT("Sheet"&{1,2,3}&"!C1:C100"),C1 ,INDIRECT("Sheet"&{1,2,3}&"!a1:a100")))

In B1:
=SUM(SUMIF(INDIRECT("Sheet"&{1,2,3}&"!C1:C100"),C1 ,INDIRECT("Sheet"&{1,2,3}&"!b1:b100")))

Select A1 & B1 copy down



"Big John" wrote:

I have a workbook with 10 different worksheets. Each worksheet has 3 columns
of data with varying number of rows.

Example:

Worksheet 1
Col A Col B Col C
1 3 TextA
7 0 TextB
1 0 TextE

Worksheet 2
Col A Col B Col C
1 0 TextA
1 0 TextB
7 1 TextC
2 2 TextE
1 0 TextF

Worksheet 3
Col A Col B Col C
3 2 TextG
5 5 TextN

etc...

Columns A and B are counts of the description in Column C.

Column C will only be present if there is a positive value in Col A.

I want to have a summary sheet that will have sum of the counts in Column A
and Column B that correspond with the definition in Column C

Summary
Col A Col B Col C
2 3 TextA
8 0 TextB
7 1 TextC
3 2 TextE
1 0 TextF
3 2 TextG
5 5 TextN

I believe I need to use a LOOKUP function, but have been unsuccessful. I
can change the output so Column C is actually the first column and then have
the counts follow (figuring that may help me use the VLOOKUP function).

I've put all of the worksheet names in cells and tried referencing them by
name (also like the trick I read where you have a Start and End worksheet and
hide them) as to be able to easily reference all the sheets, but I think I'm
making things too difficult.

Any help/guidance would be greatly appreciated.

TM,

I'm trying to get your solution to work as it seems it will require less
data entry. Here is what I'm using...

=SUM(SUMIF(INDIRECT("Start:End!D4:D100"),D4,INDIRE CT("Start:End!B4:B100")))

=SUM(SUMIF(INDIRECT("Start:End!D4:D100"),D4,INDIRE CT("Start:End!C4:C100")))

My example was simplified, my criteria is actually in Column D with the
numbers I want to have summed up are in Columns B and C. I created a Start
and End worksheet and hid them as I figured that would be easier than
entering the 25 different worksheet values.

I get the dreated #REF error, so somehow I'm referencing some invalid data.

Any ideas where/how/what I'm doing wrong?

THANKS!

"Teethless mama" wrote:

In Summary sheet:

Criteria start in C1

In A1:
=SUM(SUMIF(INDIRECT("Sheet"&{1,2,3}&"!C1:C100"),C1 ,INDIRECT("Sheet"&{1,2,3}&"!a1:a100")))

In B1:
=SUM(SUMIF(INDIRECT("Sheet"&{1,2,3}&"!C1:C100"),C1 ,INDIRECT("Sheet"&{1,2,3}&"!b1:b100")))

Select A1 & B1 copy down

TM,

Here's what I ended up getting to work by putting my worksheet names in AA1
to AA21 on my summary worksheet.

=SUMPRODUCT(SUMIF(INDIRECT("'"&$AA$1:$AA$22&"'!$D$ 4:$D$100"),D4,INDIRECT("'"&$AA$1:$AA$22&"'!$B$4:$B $100")))

=SUMPRODUCT(SUMIF(INDIRECT("'"&$AA$1:$AA$22&"'!$D$ 4:$D$100"),D4,INDIRECT("'"&$AA$1:$AA$22&"'!$C$4:$C $100")))

Thanks for everyone's help!

John