Sumproduct with Vlookup

[cwallace70]

I have a summary sheet with 12 monthly worksheets that I want a sum of the following. It contains 3 criterias.
The summary sheets have a dept code of "EN" and I want a sum from all 12 monthly worksheets that have that same code in column C with an amount in column E.
Ca nthis be done with sumproduct and vlookup or just sumproduct?

 

[barry houdini]

What are the 12 worksheets called?

 

[cwallace70]

What are the 12 worksheets called?

Jan thru Dec.

 

[barry houdini]

Try this formula

=SUMPRODUCT(SUMIF(INDIRECT("'"&TEXT(ROW(INDIRECT("1:12"))*29,"mmm")&"'!C:C"),"EN",INDIRECT("'"&TEXT(ROW(INDIRECT("1:12"))*29,"mmm")&"'!E:E")))

 

[Aladin Akyurek]

I have a summary sheet with 12 monthly worksheets that I want a sum of the following. It contains 3 criterias.
The summary sheets have a dept code of "EN" and I want a sum from all 12 monthly worksheets that have that same code in column C with an amount in column E.
Ca nthis be done with sumproduct and vlookup or just sumproduct?

You could create a range housing the names of the relevant sheets, which you want to conditionally sum. Name this range somethink like SheetList2010. Then invoke:

=SUMPRODUCT(SUMIF(INDIRECT("'"&SheetList2010&"'!C2:C100"),"EN",INDIRECT("'"&SheetList2010&"'!E2:E100")))

 

[T. Valko]

Jan thru Dec.

Create this defined named formula:

• Name: Sheets
• Refers to: =TEXT({1,2,3,4,5,6,7,8,9,10,11,12}*29,"mmm")

Then:

=SUMPRODUCT(SUMIF(INDIRECT(Sheets&"!C:C"),"EN",INDIRECT(Sheets&"!E:E")))

 

[cwallace70]

What are the 12 worksheets called?

Create this defined named formula:

• Name: Sheets
• Refers to: =TEXT({1,2,3,4,5,6,7,8,9,10,11,12}*29,"mmm")

I am totally confused now, Where am I inputting the formula you have above? highlighted in red

Then:

=SUMPRODUCT(SUMIF(INDIRECT(Sheets&"!C:C"),"EN",INDIRECT(Sheets&"!E:E")))

I get this one

 

[T. Valko]

<TABLE cellSpacing=0 cellPadding=6 width="100%" border=0><TBODY><TR><TD class=alt2 style="BORDER-RIGHT: 1px inset; BORDER-TOP: 1px inset; BORDER-LEFT: 1px inset; BORDER-BOTTOM: 1px inset">Originally Posted by T. Valko
Create this defined named formula:

• Name: Sheets
• Refers to: =TEXT({1,2,3,4,5,6,7,8,9,10,11,12}*29,"mmm")

I am totally confused now, Where am I inputting the formula you have above? highlighted in red

Then:

=SUMPRODUCT(SUMIF(INDIRECT(Sheets&"!C:C"),"EN",INDIRECT(Sheets&"!E:E")))
</TD></TR></TBODY></TABLE>
It depends on which version of Excel you're using.

If you're using Excel 2003 or earlier:

• Goto the menu Insert>Name>Define
• Name: Sheets
• Refers to: =TEXT({1,2,3,4,5,6,7,8,9,10,11,12}*29,"mmm")
• OK

If you're using Excel 2007 or later:

• Goto the Formulas tab>Defined Names>Define name
• Name: Sheets
• Refers to: =TEXT({1,2,3,4,5,6,7,8,9,10,11,12}*29,"mmm")
• OK

 

[cwallace70]

<TABLE border=0 cellSpacing=0 cellPadding=6 width="100%"><TBODY><TR><TD style="BORDER-BOTTOM: 1px inset; BORDER-LEFT: 1px inset; BORDER-TOP: 1px inset; BORDER-RIGHT: 1px inset" class=alt2>Originally Posted by T. Valko
Create this defined named formula:

• Name: Sheets
• Refers to: =TEXT({1,2,3,4,5,6,7,8,9,10,11,12}*29,"mmm")

I am totally confused now, Where am I inputting the formula you have above? highlighted in red

Then:

=SUMPRODUCT(SUMIF(INDIRECT(Sheets&"!C:C"),"EN",INDIRECT(Sheets&"!E:E")))

</TD></TR></TBODY></TABLE>
It depends on which version of Excel you're using.


If you're using Excel 2003 or earlier:

• Goto the menu Insert>Name>Define
• Name: Sheets
• Refers to: =TEXT({1,2,3,4,5,6,7,8,9,10,11,12}*29,"mmm")
• OK

If you're using Excel 2007 or later:

• Goto the Formulas tab>Defined Names>Define name
• Name: Sheets
• Refers to: =TEXT({1,2,3,4,5,6,7,8,9,10,11,12}*29,"mmm")
• OK

Although my sheets are named Jan-Dec, would I still use the number sequence above?

 

[cwallace70]

<TABLE border=0 cellSpacing=0 cellPadding=6 width="100%"><TBODY><TR><TD style="BORDER-BOTTOM: 1px inset; BORDER-LEFT: 1px inset; BORDER-TOP: 1px inset; BORDER-RIGHT: 1px inset" class=alt2>Originally Posted by T. Valko
Create this defined named formula:

• Name: Sheets
• Refers to: =TEXT({1,2,3,4,5,6,7,8,9,10,11,12}*29,"mmm")

I am totally confused now, Where am I inputting the formula you have above? highlighted in red

Then:

=SUMPRODUCT(SUMIF(INDIRECT(Sheets&"!C:C"),"EN",INDIRECT(Sheets&"!E:E")))

</TD></TR></TBODY></TABLE>
It depends on which version of Excel you're using.


If you're using Excel 2003 or earlier:

• Goto the menu Insert>Name>Define
• Name: Sheets
• Refers to: =TEXT({1,2,3,4,5,6,7,8,9,10,11,12}*29,"mmm")
• OK

If you're using Excel 2007 or later:

• Goto the Formulas tab>Defined Names>Define name
• Name: Sheets
• Refers to: =TEXT({1,2,3,4,5,6,7,8,9,10,11,12}*29,"mmm")
• OK

Also what does the "29" and "mmm" refer to