Excel Puzzle

Guill

I am trying to do a Sumif function in a 'Summary workbook'. This works fine when the root workbooks are open but i just get #value! when i close the root workbooks.

The formula is as follows:

=SUMIF('P:\process summary\[machine 2.xlsx]Daily 2'!$E$4:$BH$738,$C$6,'P:\process summary\[Machine 2.xlsx]Daily 2'!$AS$4)

The source workbooks are fairly large sheets and this summary book i am working on pulls a lot of information from various workbooks (8 in total) and various sheets in those books.

I have spent the last couple of hours searching the web but none of the answer i see either help or make sense (Self taught on Excel).

Some sites reccomend using the =sumproduct function but i have zero ecperience with this and it makes no sense to me.

Help, anyone?

KAGY

Guill wrote: »

The formula is as follows:

=SUMIF('P:\process summary\[machine 2.xlsx]Daily 2'!$E$4:$BH$738,$C$6,'P:\process summary\[Machine 2.xlsx]Daily 2'!$AS$4)

The source workbooks are fairly large sheets and this summary book i am working on pulls a lot of information from various workbooks (8 in total) and various sheets in those books.


Help, anyone?

I'm not too sure if your formula makes sense to me: you are checking to see if the values E4:BH736 are equal to C6, if they are add a single figure AS4.
The way I see it, you will just end up with AS4 if any of those cells are equal to C6.
Something like this will work on closed sheets, it's an array formula so you have to press ctrl-shift-enter to get curly brackets on it after you've typed it. I've removed paths and absolute references to make it easier to read.

=SUM(IF('[temp.xlsx]Sheet1'!A1:B20>C6,   '[temp.xlsx]Sheet1'!A1:B20,   0))

this will add the values of all cells > c6

=SUM(IF('[temp.xlsx]Sheet1'!A1:B20=C6,   1,   0))

this will count the number of cells = c6
again ctrl-shift-enter

Guill

The way I see it, you will just end up with AS4 if any of those cells are equal to C6.


Excel compensates here, It will look down column E4 and every time it hits =c6 it runs across the same distance as AS4 was originally. It could be AS4+AS10+as20+AS21 etc..

This formula works fine for me inside a workbook but not if i want one workbook to pull from a closed one.

KAGY

Guill wrote: »

Excel compensates here, It will look down column E4 and every time it hits =c6 it runs across the same distance as AS4 was originally. It could be AS4+AS10+as20+AS21 etc..

This formula works fine for me inside a workbook but not if i want one workbook to pull from a closed one.

Makes sense, don't use it myself, prefering the array formulaes, which do work on data linked from closed sheets!

=SUM(IF('[temp.xlsx]Sheet1'!A1:B20>C6,   '[temp.xlsx]Sheet1'!AS1:AR20,   0))

I'm pretty sure this will work then (I haven't tried it), if the conditional range and the summing range have the same dimensions, this will add the values of the cells in the second range if the corresponding value in the first range is > c6