Sumproduct with Vlookup

cwallace70

Board Regular
Joined
Mar 7, 2011
Messages
155
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?
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

Board Regular
Joined
Mar 7, 2011
Messages
155

ADVERTISEMENT

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

Well-known Member
Joined
May 9, 2009
Messages
16,623
<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

Board Regular
Joined
Mar 7, 2011
Messages
155
<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

Board Regular
Joined
Mar 7, 2011
Messages
155
<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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,531
Messages
5,602,209
Members
414,513
Latest member
junbuggle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top