Combining VLOOKUPS?

[PT40]

Hello,

I am trying to tally the fields in several sheets of the same workbook. I
can use either hidden fields or link one cell to a hidden sheet with several
lookups, but would really like to put it all in one cell. I've put an example
below of what I would like to do. Is there an easy way to go about this? I'd
like to tally the cells of 7 sheets manytimes over.

Thank you

=(VLOOKUP(C4, '2-2'!$C$4:$J$25, 5, FALSE)+(VLOOKUP(C4, '2-3'!$C$4:$J$25, 5,
FALSE)+(VLOOKUP(C4, '2-4'!$C$4:$J$25, 5, FALSE)+(VLOOKUP(C4,
'2-5'!$C$4:$J$25, 5, FALSE)+(VLOOKUP(C4, '2-6'!$C$4:$J$25, 5,
FALSE)+(VLOOKUP(C4, '2-7'!$C$4:$J$25, 5, FALSE)+(VLOOKUP(C4,
'2-8'!$C$4:$J$25, 5, FALSE))

 

[Harlan Grove]

I am trying to tally the fields in several sheets of the same
workbook. I can use either hidden fields or link one cell to a
hidden sheet with several lookups, but would really like to put it
all in one cell. I've put an example below of what I would like to
do. Is there an easy way to go about this? I'd like to tally the
cells of 7 sheets manytimes over.

....

You have several more left parentheses than right parentheses, so I've
removed the unnecessary ones.

=VLOOKUP(C4, '2-2'!$C$4:$J$25, 5, FALSE)
+VLOOKUP(C4, '2-3'!$C$4:$J$25, 5, FALSE)
+VLOOKUP(C4, '2-4'!$C$4:$J$25, 5, FALSE)
+VLOOKUP(C4, '2-5'!$C$4:$J$25, 5, FALSE)
+VLOOKUP(C4, '2-6'!$C$4:$J$25, 5, FALSE)
+VLOOKUP(C4, '2-7'!$C$4:$J$25, 5, FALSE)
+VLOOKUP(C4, '2-8'!$C$4:$J$25, 5, FALSE)

Your ranges aren't too big, and if there were only one instance of the
value of C4 in the first columns of each range on the different
worksheets, you could use

=SUM(SUMIF(INDIRECT("'"&{"2-2","2-3","2-4","2-5","2-6","2-7","2-8"}
&"'!C4:C25"),C4,INDIRECT("'"&{"2-2","2-3","2-4","2-5","2-6","2-7",
"2-8"}&"'!G4:G25")))

Better, enter the worksheet names in a range, name the range W, and
shorten this formula to

=SUM(SUMIF(INDIRECT("'"&W&"'!C4:C25"),C4,INDIRECT("'"&W&"'!G4:G25")))

If your ranges were big or you could have multiple instances of the C4
value in some of these ranges, summing separate VLOOKUP calls is
optimal.

 

[PT40]

Removing the parenthesis worked. Thanks much

....

You have several more left parentheses than right parentheses, so I've
removed the unnecessary ones.


Your ranges aren't too big, and if there were only one instance of the
value of C4 in the first columns of each range on the different
worksheets, you could use

=SUM(SUMIF(INDIRECT("'"&{"2-2","2-3","2-4","2-5","2-6","2-7","2-8"}
&"'!C4:C25"),C4,INDIRECT("'"&{"2-2","2-3","2-4","2-5","2-6","2-7",
"2-8"}&"'!G4:G25")))

Better, enter the worksheet names in a range, name the range W, and
shorten this formula to

=SUM(SUMIF(INDIRECT("'"&W&"'!C4:C25"),C4,INDIRECT("'"&W&"'!G4:G25")))

If your ranges were big or you could have multiple instances of the C4
value in some of these ranges, summing separate VLOOKUP calls is
optimal.