Excluding Cells based on Format (Footbal Pool)

[Guest]

I am making a football pool tracking spreadsheet in Excel 2003. I cannot
seem to figure out three formulas in order to finish the spreadsheet. If you
need to see the spreadsheet as it stands now e-mail me at
(e-mail address removed). Once completed I am willing to share the worksheet
and instructions for it to anyone who thinks they me be able to use it.

Formula 1) I have a row that contains each in it own cell: ! , 5.00, X,
and number of wins in different places down the row, over and over. One of
the cells in the row adds the total number of wins based on numbers down the
whole row. I am trying to figure out how to exclude the 5.00 cells that is
formatted for currency so that they are not added to the total wins thus
giving an incorrect result.

Formula 2) I also have a cell that adds rows to give an overall side pot.
However, I need a modification to the sum formula that will also add 5.00 per
instance of "P" in any of the rows.

Formula 3) There is a Week number cell that I am inputting by hand based on
what week the season is at. One of my other formulas hinges on the week
number for accounting purposes. If possible I would like to automate it
somehow to update that Tuesday begins a new week, possibly starting from a
specific date (the beginning of regular season).

Any assistance is greatly appreciated.

 

[Pete_UK]

For Formula 1 I'm a bit confused with your terminology - rows go
ACROSS and columns go DOWN the sheet, but you keep refering to "down
the row".

You may be able to use +COUNTIF(range,"P")*5 for your Formula 2, but
I'm not sure what a "side pot" is.

There are a number of ways you could get a week number for your
Formula 3, but again I'm not sure exactly what you have to begin with.
Perhaps if you could try to describe your data layout more
accurately ...

I'm not sure if this helps.

Pete

 

[Guest]

LoL. Thanks for pointing that out. My brain is so fried from researching.
What you said is what I meant.

 

[Pete_UK]

Yeah, but your reply doesn't really help me to help you. Can you
please supply some more information about how your data is laid out in
your sheet.

Pete