Microsoft Excel Help Needed

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Timewell

Thread Starter
Joined
Feb 8, 2003
Messages
58
Hi guys!

I have encountered a problem with Microsoft Excel. The following formula does not seem to work for some reason and I nor another good friend can see why. Can you guys help me?

The formula is:

Code:
=COUNTIF(B59,B56,B53,B50,B47,B44,B41,B38,B35,B32,B29,B26,B23,B20,B17,B14,B11,B8,B5, ">=0")
It gives me the error message:

The formula you typed contains an error.

Go to all the usual places etc
I have noticed however that when I was clicking on the cells to give them the range, that the first three had coloured boxes around them and the rest hadn't for some reason. It keeps indicating that cell B53 has an error in it but it is a blank cell.

Many thanks for your help guys!
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
1. >0 will count cells with formulas.

2. What's in the cells in between? I.e., B6, B7, B9, B10...

I suspect you don't have your data laid out very well. Wanna attach your file so I can have a look?
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
Sorry, Time. I still don't get it.

Make a copy of your file, change the extension from xls to txt and load it up.

And if you can, put some textboxes/arrows (use the drawing toolbar) to show where you want your formulas.
 

Timewell

Thread Starter
Joined
Feb 8, 2003
Messages
58
What don't you get? You know that the spreadsheet is interactive so you could play around with the formula. It dislikes the 3rd cell in the formula I gave you and anything after it dislikes...:(
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
What don't I get?

I have no idea in which cells you need your formula or which cells you're counting if...

...and I have no idea what your worksheet does.
 

Timewell

Thread Starter
Joined
Feb 8, 2003
Messages
58
I've been told that the count if function will not work because it needs a range and not unique cells. So basically I need another function that will allow me to count individual cells while still maintaining the criteria of ">0"
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
Figuring out the formula is not the issue....

It's the layout of your file.

Why do you have 3 rows for each at the top? Why not just one row?
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
Try this:

=COUNTIF(B56:B59:B50:B53:B44:B47:B38:B41:B32:B35:B26:B29:B20:B23:B14:B17:B8:B11:B5, ">=0")
 

Timewell

Thread Starter
Joined
Feb 8, 2003
Messages
58
For that particular bit the formula now works. BUT!

For the cell which calculates the number of wins or losses, it gets interference from a cell which contains "Bonus Points".

For example, I using the formula you gave me and changing the cell references to those suiting "wins". Would mean that it calculates cells that are not needed.

Example, it calculates whether a match has been won or lost in the set of cells B5 - D5, B7 - D7 (Horizontally) and B5 - B7 and D5 - D7 (Vertically). That leaves you win a box. The criteria for a win is ">0" and thus if the score was 2 - 1, Excel would calculate it as a win.

So, I enter the score as 2 - 1. Thus Excel should say, Played 1, Won 1, Drawn 0 and Lost 0 with "For" = 2 and "Against" = 1. I have also typed in the number of bonus points to equal 3 (D5). It now gives it as For = 5 and played 2 games. I therefore need a formula to count individual cells right?
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
Sorry, time. I just can't do anymore without a real, live spreadsheet to work with.

I can't see row and column headings or anything in that online thing.
 

Timewell

Thread Starter
Joined
Feb 8, 2003
Messages
58
Is there another way of sending it to you or don't you want me to mail it? It won't upload for some reason and CSV files don't show the formula.

So can I mail it you?
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top