SUM the vlookup result across sheet

[sadath]

Hi

The the CSE formula to sum the vlookup result is not working

=SUM(VLOOKUP(A5,INDIRECT(INDEX(sh,ROW(1:5))&"!A4:B500"),2,0))

where sh has sheet 5 sheet names

Any alternate formula ?

 

[sadath]

I tried this too, but not getting the sum of results

{=SUM(SUMIF(INDIRECT(INDEX(sh,ROW(1:5))&"!A4:A500"),A5,INDIRECT(INDEX(sh,ROW(1:2))&"!b4:b500")))}

 

[Aladin Akyurek]

Hi

The the CSE formula to sum the vlookup result is not working

=SUM(VLOOKUP(A5,INDIRECT(INDEX(sh,ROW(1:5))&"!A4:B500"),2,0))

where sh has sheet 5 sheet names

Any alternate formula ?

Enter the sheet names in a range of its own and name that range SheetList via the Name Box, then invoke:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A4:A500"),A5,INDIRECT("'"&SheetList&"'!B4:B500"))

 

[sadath]

Thank You Aladin!
its working fine

 

[Aladin Akyurek]

Thank You Aladin!
its working fine

Great. Thanks for providing feedback.

 

[pauljamestg]

Enter the sheet names in a range of its own and name that range SheetList via the Name Box, then invoke:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A4:A500"),A5,INDIRECT("'"&SheetList&"'!B4:B500"))

Could someone explain what this formula does? I am not able to figure it out.

 

[Aladin Akyurek]

Could someone explain what this formula does? I am not able to figure it out.

Suppose we have:

Sheet1, A4:B6

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19 width=64>a</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64 align=right>3</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>a</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=19>b</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>5</TD></TR></TBODY></TABLE>

Sheet2, A4:B6

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64>a</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>6</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>b</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>4</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>b</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>8</TD></TR></TBODY></TABLE>

Goal. Calcukate a conditional total for item a across Sheet1 and Sheet2.

The expected result is: 11

Let's list Sheet1 and Sheet2 in A2:A3 on Sheet3 and let A5 house "a", the item of interest. Let's name the range A2:A3 as SheetList.

Let A6 house:

[1]

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList&"'!A4:A500"),A5,INDIRECT("'"&SheetList&"'!B4:B500"))

This formula is effectively equivalent to:

[2]

=SUM(SUMIF(Sheet1!A4:A6,A5,Sheet1!B4:B6)),SUMIF(Sheet2!A4:A6,A5,Sheet2!B4:B6))

Select the argument of the first INDIRECT on the Formula Bar and hit the F9 function key. Do the same with that of the second INDIRECT. The formula would like as follows at this point:

=SUMPRODUCT(SUMIF(INDIRECT({"'Sheet1'!A4:A6";"'Sheet2'!A4:A6"}),A5,INDIRECT({"'Sheet1'!B4:B6";"'Sheet2'!B4:B6"})))

INDIRECT feeds SUMIF with relevant pairs of ranges, that is Sheet1 and Sheet2 ranges separately.

Select the SUMIF bit and hit F9. The intermediate result would look like:

=SUMPRODUCT({5;6})

showing the separate results for each sheet. SUMPRODUCT sums these values, yielding 11 as result, as would obtain with [2].

Hope this helps.

 

[pauljamestg]

Thanks much for taking time out to explain this to me. It is clear now.

I am new to the array formula "{}" and the way we use INDIRECT("'"&SheetList&"'!A4:A500"), though I have been using Excel for quite some time. Could you give me any links where I can gain more knowledge on these? Thanks again!

BTW, I am also from Hague

 

[Aladin Akyurek]

Thanks much for taking time out to explain this to me. It is clear now.

You are welcome.

I am new to the array formula "{}" and the way we use INDIRECT("'"&SheetList&"'!A4:A500"), though I have been using Excel for quite some time. Could you give me any links where I can gain more knowledge on these? Thanks again!

Forums like this one contain many example uses of such and similar formulas along with the reasonably clear problems for which they are devised. Re-doing them on your system might help to advance your knowledge.

BTW, I am also from Hague

I can be reached at Haagse Hogeschool. Tot ziens.