SUMIF

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Hi,

1)
Thanks to Tom Ogilvy who posted the earlier reply about the SUMIF function.

This is what I was looking for, however it dosnt seem to handle text very
well.

What I want to do is to count the number of times a string appears in a
column of data ( in a different workbook).

However the way this string has been input means that there may be white
space at the start or end of the string.

Is there any way to get the SUMIF function to say

IF A contains B, THEN ++ ?? ( as opposed to IF A = B).

2)
Also is there anyway to work with spaces - I have a lot of worksheets to be
referenced but they all contain spaces, and currnetly it wont let me work
with these.


3)
Is there any neat way of referencing the current sheet in a formula.
See I have 2 Excel files, A , B. Both files have exactly the same number of
worksheets ( each worksheet in A has a coressponding one in B, named
identically).

So what I want to do is to reference the coressponding worksheet in the
other file from the current worksheet, without explicitly naming that
worksheet ( so I can just copy it to all other worksheets - I will
eventually have over 60 !! )

Thanks
 
Dave wrote
Hi,

1)
Thanks to Tom Ogilvy who posted the earlier reply about the SUMIF function.

This is what I was looking for, however it dosnt seem to handle text very
well.

What I want to do is to count the number of times a string appears in a
column of data ( in a different workbook).

However the way this string has been input means that there may be white
space at the start or end of the string.

Is there any way to get the SUMIF function to say

IF A contains B, THEN ++ ?? ( as opposed to IF A = B).
Click to expand...

Look at the TRIM function. Without knowing exactly what you require I
am not sure where the best place for you to use it would be but it will do
what you want.

Maybe you need a macro called Clean that you use to tidy up your
worksheets a little. It could remove all the leading spaces etc. Then
you can work with your data knowing that it is correctly formatted.


2)
Also is there anyway to work with spaces - I have a lot of worksheets to be
referenced but they all contain spaces, and currnetly it wont let me work
with these.
Click to expand...

Do you mean spaces in workbook names? ... in worksheet names? If not, where
are these spaces? You could remove them with a find/replace macro.

3)
Is there any neat way of referencing the current sheet in a formula.
See I have 2 Excel files, A , B. Both files have exactly the same number of
worksheets ( each worksheet in A has a coressponding one in B, named
identically).
Click to expand...
So what I want to do is to reference the coressponding worksheet in the
other file from the current worksheet, without explicitly naming that
worksheet ( so I can just copy it to all other worksheets - I will
eventually have over 60 !! )
Click to expand...

If both workbooks are open the you can do this:

Add a named range to the worksheet by using menus Insert --> Name --> Define
Give the range the name xxx!SheetName where xxx is the current sheet name.
In the refers to text box enter a cell reference where you can put the name of the
sheet. If the sheet name has spaces in it then use single quotes around the sheet
name like this 'xxx xxx'!SheetName

When you want to use the sheet name in a your formula then use this
assuming your other workbook is called X1:

=INDIRECT("[X1.xls]" & SheetName & "!A1")

If you make sure your range called SheetName can be in the same place on
each worksheet then you can create one name and get Excel to do the
others for you. To do that you enter =SheetName in a cell on the
worksheet where you inserted that name. Now copy this cell with the
formula in it and paste it to each other worksheet. It will create the
name for you. You can delete the formula once you have pasted it
to each sheet cos it has no useful purpose.

Now you just have to put the sheet name into each of these cells.

Both workbooks need to be opened to use the indirect function as
it does not work on a closed book.
 
Back
Top