Help with Excel Formula

[BG12424]

I am trying to create a formula that will allow me to sort a live results page

I would like to sort the Live Results tab based on the Total Mileage. I have another tab ("Log tab") that I enter the details for.

The Log sheet has a list of names, then based on the number of "x" I enter aside their name, auto-calculates the number of miles traveled.

I'd like my live results sheet to be sorted based on the total miles traveled, but needs to know, after sorting, which row to calculate from the log sheet for a particular person.

I'm thinking a combination of a VLOOKUP and COUNTIF, but can't seem to get it to work. I've supplied a link to the Google spreadsheet where I'm trying to accomplish this if you could have a look. I will certainly award a star smile.

Please see the following spreadsheet (Live Results and Log tabs) attached.

Thanks in advance and I hope I'm explaining this clearly enough.

Brian

regards,
Brian

http://www.newleaftechinc.com

 

[SkipVought]

Hi,

Some of us who would help, can't, due to our company's access restrictions.

So if you would like to broaden the responses potential, please post some consistent data examples from your workbook, along with the formula(s) that are not working.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

I tried to look at the file, but you have it locked down. In Google Docs, at the top right, go to Share > Get the link to share and make sure that you have Allow anyone with the link to view checked.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[BG12424]

My extreme apologies for the locked down file. I made a copy and thought permissions carried over. Sorry again.

http://spreadsheets.google.com/ccc?key=ruYIANMmIKrrevnVRX8ECKg&hl=en

regards,
Brian

http://www.newleaftechinc.com

 

[BG12424]

log sheet:

columns:
name (col a)
col1_1 (col b)
col1_2 (col c)
col1_3 (col d)
col2_1 (col e)
col2_2 (col f)
col2_3 (col g)

live results sheet:

columns:
name (col a) < currently static, but would be nice to link >
totCol1 (col b) Formula: countif(Log!b2:d2,"x")
totCol2 (col c) Formula: countif(Log!e2:g2,"x")
total miles (col b) Formula: (b2*2.28)+(c2*0.5)

What I would like totCol1 to be is:
if (col a = logsheet!name) then count the number of #'s and keep this logic when sorting on the live results sheet against total miles.

Thanks

Here is a simplistic explanation



regards,
Brian

http://www.newleaftechinc.com

 

[SkipVought]

Thanks.

You DESCRIBE the sheet and formulas, OK

But you did not post, "some consistent data examples" from the two sheets.

your formulas look OK.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BG12424]

Log Sheet

A2: Brian
A3: John
A4: Tim

Live Results (unsorted)

A2: Brian
A3: John
A4: Tim

Looks fine, but when...

Live Results (sorted)

A2: Tim
A3: John
A4: Brian

My formulas are still looking at Log!a2, but that sheet is sorted differently, so the name does not match up any longer on my Live results sheet. I need to somehow relate wherever, say, "Brian" is on the Log sheet to get the countif's to work. Maybe with a VLOOKUP or something, but not sure how to get this to work.

Thanks


regards,
Brian

http://www.newleaftechinc.com

 

[SkipVought]

Try this
[tt]
=SUMPRODUCT((Log!$A$2:$A$4=$A2)*(Log!$B$2:$D$4="x"))
[/tt]


Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

next time it would be helpful if your posted your examples...
[tt]
log sheet:
col1_1 col1_2 col1_3 col2_1 col2_2 col2_3
Brian x . x . x x
John x . . x x .
Tim x x x . x x
[/tt]
I had to patch together from several of your posts, thank you very much!

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

and what is the reason for columns named...
[tt]
col1_1 col1_2 col1_3 col2_1 col2_2 col2_3
[/tt]
Just wondering.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[BG12424]

Yes, was making it generic for example purposes.

I just ended up using a VLOOKUP to get what I wanted.

=vlookup($B23,log_datasheet,35,false)

Thanks

regards,
Brian

http://www.newleaftechinc.com

 

[SkipVought]

Then youe don't care about sorting your list?

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]