IF(AND) formula help

[Barty1884]

Hi everyone,

Really struggling to put this formula together - and I'll need to replicate it for a number of fields.....hopefully someone can assist

What I'm looking to say is:
"If the value in the D column is equal to 1 or 2 and the value in the B column (in the same row) is also equal to 1 or 2 then return 5. If the D column is equal to B column and B column is more than 2, then return 1 (again, same row)"

Needless to say there will be multiples (32 separate results infact) and I'd like the cell/formula to keep a running total...

Hopefully what I'm asking makes sense?

[gak67]

Please Login or Register  to view this content.

Should do it. Not sure what you mean by the running total. You also haven't said what you want to happen if the conditions you specified are not met, so I had it return a blank cell as a response.

[Barty1884]

Hi gak67, it doesn't appear to work unfortunately - although I'm not sure I've really explained what I'm looking for properly.

I'm looking for a single field to hold the formula and compile a running total. What I have is 4 rows (with a B column and a D column value) that have the ability to return 5, 5, 1 & 1 (assuming all are correct) that I want to display as a total in the 5th row

Does that even make sense in the slightest? I'm really struggling to explain...

[Barty1884]

Independently of each other also, so I'm not trying to say "if one of the B fields match one of the D fields then return X", I'm only comparing D1 with B1, D2 with B2 etc.

[gak67]

If you upload a sample file we'll get a better idea of what you want. Click on Go Advanced (bottom right) then on the paperclip.

[Barty1884]

example file.xls

Hopefully that makes sense?

It's for an office world cup pool - group stages, where our criteria is
If the team progresses (ie finishes 1st or 2nd), then you get 5 points
For the 3rd and 4th placed, you have to have the placing correct and it's worth 1 point.

Obviously there are 8 groups and there will be multiple people entering, so I'm trying to establish a formula that can be replicated

[gak67]

I see what you are trying to do. The formula I gave you (with some range modifications) will give you the result for each participant if you used a helper column, but I can understand that you don't want to use a helper column for each participant. I think you need a SUMPRODUCT or an array formula. I know my limitations, and I can't really help with those (although it's something I want to learn about). Hopefully somebody else here picks this up.

[gak67]

Ok, I've done some research and I can get it working for you, but it's not very tidy.

Please Login or Register  to view this content.

[gak67]

Tidied up a bit:

Please Login or Register  to view this content.

You could put that at the top of each pool to get a total for each pool and then add those for an overall total, or if you just want an overall total, extend the range as follows:

Please Login or Register  to view this content.

[Barty1884]

Thanks gak! Looks like that does exactly what I was looking for! Appreciated!