Nested Formulae IF->AND

Hi

I am trying to produce a series of reports based on the results from a group of gamers but it goes a bit beyond my very basic knowledge of spreadsheeting.

I have used array formulae to extract results from the full list which goes something like this;

{=SUM(IF(E8:E57="player1",F8:F57))}

I will add columns to make calculations based on the product which need to ignore columns in which no result has been posted. As a zero score is possible in the game I am trying to add a condition which will return a non-numerical value or better still a blank when False. I think ‘=IF((AND))’ would work but I can’t get the syntax sorted. Is this because it can’t be nested with an array formula?

(It maybe that a VLOOKUP option would work better but I’ve tried & failed to grasp how to make that work and as this may ultimately involve collating results from several worksheets I’m even less sure how VLOOKUP might work there.The SUM option works because each player can only post one result in any column - but I admit it’s ugly.)

NB. I'm running Office 2000

You kind of did this the hard way. See attached for a simpler example. For your first formula, a simpler approach would be to put this formula in B8 and copy down:

=VLOOKUP(A8,D:E,2,FALSE)

Your formula requires you to type each player's name into the formula, which has obvious drawbacks.

This solution returns #N/A when the name is not found, which can be tested for in a formula like this:

=IF(ISNA(B8),"",<some formula goes here>)

I don't follow what you're ultimately trying to do so I can't offer specific help on how to do it.

Yes, I started out with

=SUMIF(D:D,A8,E:E)

but then I noticed that the OP said each name appeared only once, so VLOOKUP works. However, SUMIF will give 0 if the name is not found, which might be a little easier to deal with.