Sumproduct across multiplesheets

**khateeb** · 05-30-2013, 08:34 AM

Dear All, Please kindly respond me. I am beating my head since day, and hoping may be I can get help in this forum.
I would like to do sumprodcut across multiple sheets (Date1 : Date 31). These dates are selected by drop down list (Data validation)
Sum function which is happening in Sheet “Date2” E16 to I 19, I would like that sum to take place in sheet “summary2” B8 to E11. I want similar function to take place in Summary2 for all the sheets selected by date(dropdown list).

Note: I will delete that function from sheets (Date1 to Date 31), because I don’t want it to be there. So, this won’t work for me (=Date2!E16) which is simply refering to that cell.

For more details please find the attached sheet.

**jubiesxl** · 05-30-2013, 07:07 PM

perhaps the Data Consolidation or Pivot Table across multiple sheets would be place to start with before using sumif or sumproduct

**FDibbins** · 05-30-2013, 08:06 PM

The table you mentioned on date1 is not the same as any other table on any other sheet?
and you could replace...
=SUM(SUMPRODUCT(($E$3:$H$14="yes")*($I$3:$I$14=B$16)*($E$2:$H$2=E$2)))
with...
=COUNTIFS(E$3:E$14,"yes",$I$3:$I$14,$B16)

It might help if you provided a few sample answers, and explain how you arrived at them?

**vlady** · 05-30-2013, 11:33 PM

using countifs and indirect based on your drop down.
look attachment

test (2).xlsm

**khateeb** · 05-31-2013, 04:10 AM

WHAT is the use of doing it per date wise. We can already do it from and to date. Aren't we creating more data by doing it for single date 1 sheet and sum of dates sheet.

**vlady** · 05-31-2013, 04:27 AM

sorry i think i lost what you are doing.

the summary formula i provided works only for all building 1.1.1 across to the date from and to date dropdown.

my suggestion inside the sample file was if ever you want to summarized all Building in a certain date you have to have a computation inside each date (summary)

eg.
in date1 summary

IN CELl A1000=(building 1.1.1 black,MF8330) +(building1.1.2 black,MF8330)+building1.1.3 black,MF8330)etc.

so the total will be in a single cell that you can sum across all the date tabs using same procedure found in summary2 the date from --date to drop down with sumproduct(indirect())