Excel Syntax in formula

[DanAuber]

I have a spreadsheet with pathnames in Column A, filenames in Column B and a datafield in Column C

In Column D I want to write a Sumif Statement as follows:

=SUMIF(Pathname/Filename/Sheet1$B$Y,datafield,Pathname/Filename/Sheet1$Y$Y)

but I want to use the data in the other columns

so it would be:

=SUMIF(ColumnARow1/ColumnBRow1/Sheet1$B$Y,ColumnCRow1,ColumnARow1/ColumnBRow1/Sheet1$Y$Y)

I'm having great difficulty with the syntax (using cellreferences to link to pathnames inside a SUMIF function.)

Can anyone help

Thanks

Dan

 

[AnotherHiggins]

Have a look at the INDIRECT function.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[DanAuber]

Thanks John - I have tried using the INDIRECT function - I did think that would be the way to go - but I'm still struggling with the actual syntax in terms of apostrophes etc

 

[GlennUK]

Dan,

using INDIRECT won't work if you are planning on having the other source book closed during calculation. It must be open ... is that OK?

On the data ... what is Sheet1$B$Y? Surely the range to test should be a single column, as in Sheet1!$B:$B ?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[DanAuber]

Hi Glenn,

If there is a way to do it without having to have the sheets open that would be best. If not I'm happy to make do with them needing to be open.

The Sumif Function takes a range as it's first variable this is generally a range of columns as in B to Y. The second variable is the actual value you want to find in the B column, and the third variable is the column to sum up - in my case column Y. Hope this makes sense

Dan

 

[GlennUK]

The Sumif Function takes a range as it's first variable this is generally a range of columns as in B to Y.

I repeat my comment from previous post:

Surely the range to test should be a single column, as in Sheet1!$B:$B?

... and from the Help:

range Required. The range of cells that you want evaluated by criteria.

... according to your own description only column B needs to be evaluated by criteria.


What are the current contents of cells A1, B1, and how are you specifying the sheet and column references ( you examples to now haven't been proper references )?



Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[DanAuber]

Hi Glenn,

I haven't really got an issue with the SUMIF function - I use it every day quite happily.

Maybe if I explain the problem I have differently:

If Cell A1 has a filepath and Cell B1 has a filename. I'd like Cell C1 return whatever is in Cell A1 from the filepath/filename specified in A1 and B1. So I'd need a formula in C1 something like = filepath/filename!A1 - but using A1 and B1 (so something like = "A1B1"!A1) - it's the actual syntax that would get this to work that I'm struggling with.

So - A1 might have C:\directory123
and B1 might be filenameABC
then C1 would return whatever is in cell A1 from C:\directory123\filenameABC

I hope this makes things clearer.

thanks

Dan

 

[GlennUK]

To do a SUMIF with INDIRECT, have a closing "\" on the end of you folder string in cell A1, have "[" and "]" around your filename in B1 ( and remember to put the extension .XLS on the end, unlike in your example ).

=SUMIF(INDIRECT("'"&A1&B1&"Sheet1'!$B$1:$B$20"),D1,INDIRECT("'"&A1&B1&"Sheet1'!$Y$1:$Y$20"))

Can you see that I have specified a single column for the criteria to search for matching, just as the Help says.


Cheers, Glenn.

Beauty is in the eye of the beerholder.