Using SUMIFS across Multiple sheets - Problem in re-opening

[pankaj.d.m]

HI everyone,

Using the SUMIFS function, I could successfully get the expected results from extracting data from 15 base separate excel files (from where the data will be extracted).
However, whenever I am re-opening my master sheet(where I want the data to reflect) it gives me #VALUE!

Only after opening all my 15 base sheets, my master sheet fetches data.

Can anyone help me with some solution on the above problem ?
Or can anyone suggest an alternative function to it ?

Regards,
Pankaj.Master Sheet Templete.xlsx

[etaf]

Sumifs will not work on closed workbooks
But Sumproduct() will - can you change the SUMIFS to use SUMPRODUCT(() , i'm not an expert with sumproduct() - so I may not be able to help - but if you post the formula here, i'm sure others will convert it for you

[pankaj.d.m]

Thanks for the reply.
Please note the formula as mentioned below:

=SUMIFS('[Neeraj Gupta.xls]Timesheet'!$F$4:$F$3000,'[Neeraj Gupta.xls]Timesheet'!$C$4:$C$3000,B5)+
SUMIFS('[Ashmita Parab.xls]Timesheet'!$F$4:$F$3000,'[Ashmita Parab.xls]Timesheet'!$C$4:$C$3000,B5)+
SUMIFS('[Fahad Ansari.xls]Timesheet'!$F$4:$F$3000,'[Fahad Ansari.xls]Timesheet'!$C$4:$C$3000,B5)

I have posted the formula for fetching data from 3 separate sheets, for sake of understanding.

As you said correctly that it does not work with closed sheets, I need an alternative to it as it is neither convenient nor possible at all times to open all the 15 sheets before opening the master sheet.

Regards,
Pankaj.

[Ace_XL]

Try

=SUMPRODUCT('[Neeraj Gupta.xls]Timesheet'!$F$4:$F$3000*('[Neeraj Gupta.xls]Timesheet'!$C$4:$C$3000=B5))+
SUMPRODUCT('[Ashmita Parab.xls]Timesheet'!$F$4:$F$3000*('[Ashmita Parab.xls]Timesheet'!$C$4:$C$3000=B5))+
SUMPODUCT('[Fahad Ansari.xls]Timesheet'!$F$4:$F$3000*('[Fahad Ansari.xls]Timesheet'!$C$4:$C$3000=B5))

@etaf

Sumifs will not work on closed workbooks
But Sumproduct() will -

Didn't know that, thanks

[etaf]

hope i'm right

[pankaj.d.m]

Thanks for the hint.

But it SUMPRODUCT shall give me Sum of Products, unfortunately I only need pure summation only.

Thanks anyways. Do let me know if there is any other way to get around this problem.

Regards,
Pankaj.

[Tony Valko]

Sumifs will not work on closed workbooks

hope i'm right

You are!

Off the top of my head, other functions that will not work on closed files:

SUMIF(S)
COUNTIF(S)
AVERAGEIF(S)
INDIRECT
OFFSET

[Tony Valko]

But it SUMPRODUCT shall give me Sum of Products, unfortunately I only need pure summation only.

SUMPRODUCT can give you the same thing as SUMIF.

Data Range

	A	B	C	D
1	Yes	95		292
2	No	86
3	No	47
4	Maybe	43
5	Yes	70
6	Maybe	50
7	Yes	94
8	Yes	33
9	No	49
10	Maybe	51
11	------	------	------	------

Entered in C1:

=SUMPRODUCT(--(A1:A10="Yes"),B1:B10)

Which is the same as:

=SUMIF(A1:A10,"Yes",B1:B10)

[pankaj.d.m]

Thanks a ton Tony.

Your proposed solution worked perfectly fine.

May u please also confirm, if I need to put more than 1 condition to check while fetching data, is it possible using SUMPRODUCT function ?

For eg. in ur example, say I need to fetch data with following condition.
If the data says YES and If its above 70 --> Return value with summation of all the cells qualifying both conditions.

Thanks once again.
Regards,
Pankaj.

[Tony Valko]

Like this...

=SUMPRODUCT(--(A1:A10="Yes"),--(B1:B10>70),B1:B10)

Here's some more info on how powerful the SUMPRODUCT function is:

http://xldynamic.com/source/xld.SUMPRODUCT.html

[oeldere]

or use a pivot table for it.

[pankaj.d.m]

Thanks TONY.

Ur suggestions are really helpful.

Regards,
Pankaj.

[Tony Valko]

You're welcome. Thanks for the feedback!