# Im sure the answer is out there!!!!

#### JoshuaMars

##### New Member
Hi there Excel users,

Im trying to complete this formula.

Im sure its easy for someone out there with more expertise then me.

=SUMIF('Sports Daily'!B390:'Sports Daily'!B466,"=*ATP*",'Sports Daily'!C390:'Sports Daily'!C466)

What Im trying to work out is the ATP area of it. ATP is in a cell that comes from a workshet name.
=IF(COUNTA(SHEETS)>=ROW(\$A1),INDEX(SHEETS, ROW(\$A1)), "")

So basicly, the ATP is a workshet name, and if i change the name of the worksheet, the cell changes and so does the SUMIF formula.

HELP me please anyone. Im lost.

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### hatman

##### Well-known Member
Your question is not all that clear... but are you maybe looking for the Indirect() function here?

#### Peter_SSs

##### MrExcel MVP, Moderator
Joshua

I also am finding your requirement hard to understand. However, perhaps you could try this:
=SUMIF('Sports Daily'!B390:B466,"*ATP*",'Sports Daily'!C390:C466)

#### JoshuaMars

##### New Member
Thanks Peter,

My formula is now as follows.
=SUMIF('Sports Daily'!B390:B466,"=*ATP*",'Sports Daily'!C390:C466)

What Im trying to do is SUM the total of Cells B390 to B466 in the rows with the word ATP in it in the worksheet titled Spors Daily.

The cell with the word ATP has the current formula.
=IF(COUNTA(SHEETS)>=ROW(\$A1),INDEX(SHEETS, ROW(\$A1)), "")

As the word ATP is also the name of a workshet.

But what I would like to is change the top formula it so that if I change the ATP workshet to another name it also changes the ATP in the top formula.

At the moment, the seceond formula automaticly changes.

I was thinking that maybe I somehow join both formulas.

HELP!!!!!!!

#### hatman

##### Well-known Member
Is THIS what you are looking for? Trying to count all cells that contain the Name of sheet ATP no matter what it gets changed to?

Code:
=SUMIF('Sports Daily'!B390:'Sports Daily'!B466,"=*"&RIGHT(CELL("filename",'ATP!'!A1),LEN(CELL("filename",'ATP!'!A1))-FIND("]",CELL("filename",'ATP!'!A1)))&"*",'Sports Daily'!C390:'Sports Daily'!C466)

#### JoshuaMars

##### New Member
hmmmm, close. I hope this is the right explaination.

Trying to count all cells that contain the Name of sheet "x" in the sheet "Sports Daily" no matter what sheet "x" it gets changed to.

At the moment Ive worked out
=SUMIF('Sports Daily'!B390:B466,"=*x*",'Sports Daily'!C390:C466)

Is it possible to be a formula?

#### hatman

##### Well-known Member
Yup, that's what my proposed solution does... assuming that the sheet is named ATP at this moment...

EDIT: NOPE! I got over-confident, and forgot that you can't use wildcards in the Countif() formula... i'll post something more useful in a sec.

#### hatman

##### Well-known Member
Try THIS
Code:
=SUM(IF(ISNUMBER(SEARCH(RIGHT(CELL("filename",'ATP!'!A1),LEN(CELL("filename",'ATP!'!A1))-FIND("]",CELL("filename",'ATP!'!A1))),'Sports Daily'!B390:B466)),'Sports Daily'!C390:C466,0))
confirmed with Ctrl+Shift+Enter rather than just Enter.

#### JoshuaMars

##### New Member
I tried to enter this code but it came up with a syntex error.

I already have acode in the worksheet. How do I add another?

Thanks

#### hatman

##### Well-known Member
Sorry, I see I added a couple of extra !'s in there... try this EXACTLY... and be sure that your workbook is saved... otherwise this won;t work.

Not sure what you mean by "acode".

Replies
5
Views
446
Replies
4
Views
370
Replies
0
Views
269
Replies
5
Views
352
Replies
0
Views
175

1,171,968
Messages
5,878,537
Members
433,352
Latest member
horna

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back