Im sure the answer is out there!!!!

JoshuaMars

New Member
Joined
Jul 13, 2006
Messages
46
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
Your question is not all that clear... but are you maybe looking for the Indirect() function here?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,424
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 13, 2006
Messages
46
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
Joined
Apr 8, 2005
Messages
2,664

ADVERTISEMENT

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
Joined
Jul 13, 2006
Messages
46
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?

Thanks in advance.
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664

ADVERTISEMENT

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

EDIT: NOPE! :oops: 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
Joined
Apr 8, 2005
Messages
2,664
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
Joined
Jul 13, 2006
Messages
46
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
Joined
Apr 8, 2005
Messages
2,664
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".
 

Forum statistics

Threads
1,136,369
Messages
5,675,359
Members
419,565
Latest member
Phil57

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top