numbers query. spreadsheet help

Question

BillyCasper Author

Level 1

4 points

numbers query. spreadsheet help

Hello,

I need some help tweaking a spreadsheet that I've created.

Ive created a running log and all so far it does the simple things ive asked it to do.

however..

I would like to keep a track of how many miles a running shoe does.

so I created a drop down menu so I can pick which shoe I wore on any given run, but what is the formula that would auto add the miles to the next cell along, depending on which shoe I pick.

for instance

if I pick shoe A then it only adds up the miles where i have made that choice

currently the spreadsheet has

miles - column C

shoe choice - column N

I would like the total miles for a shoe on column O..

I hope this makes sense.

Thank you for reading

MacBook Air 13″, macOS 15.0

Posted on Oct 20, 2024 1:26 AM

Reply

Answer 1

Yellowbox

Level 7

21,029 points

Oct 20, 2024 6:55 AM in response to BillyCasper

Hi Billy,

BillyCasper wrote:

Thank you Ian for this detailed reply.. I am only a beginner when it comes to spreadsheets. so I have some questions.

The sumif option is certainly the answer and I really should get friendlier with it but,

the summary table has confused me.

I used the Summary table because the Numbers way is several small tables, each with a purpose (as opposed to the Excel way with a huge sheet (an "ocean" with "islands" of data here and there).

I can't seem to choose between shoes when formulating, I have sumif, test value, condition, sum value.

if test value is both shoe options and condition is shoe chosen, how do I get to the drop down for my choices. I don't have the summary table..

apologies if its a dumb question

Billy

There are no dumb questions in this forum. Sorry for my dumb reply (I didn't read your original post more carefully).

OK, two shoe types A and B.

Let's go with your original post:

"currently the spreadsheet has

miles - column C

shoe choice - column N

I would like the total miles for a shoe on column O."

Here we go:

Column B is Pop-Up Menus (Excel calls them Drop Down Menus 😎)

Choose a shoe type.

Column C is Miles on that shoe for each run.

Column N is shoe type for the Summary.

Column O is the Summary (Miles for each shoe type)

SUMIF(B,N2,C)

SUMIF(test-values, condition, sum-values)

Please reply with questions.

Happy Numbering!

Ian.

Reply

Answer 2

Yellowbox

Level 7

21,029 points

Oct 21, 2024 4:26 AM in response to BillyCasper

Hi Billy,

Thanks for the screenshot. I assume that you want to sum (in Column O) the distances (miles) in Column C for each shoe type in Column N.

Questions:

How many shoe types? I see P, A, 1080v12, 680v6 in Column N. Neither P nor A appear in the Pop-Up Menu in Column N. How did they get there?

You don't need the whole of Column O for the results. That is why I suggested a separate table (Summary) in my first reply. If you don't want a separate table, we can use Footer Rows (Numbers allows up to 5 Footer Rows per table) in the main table.

Still using SUMIF, formula in O11 is SUMIF(N,N11,C)

Fill down.

Are we getting closer to your overall aim?

Regards,

Ian.

Reply

Answer 3

Yellowbox

Level 7

21,029 points

Oct 20, 2024 4:43 AM in response to BillyCasper

Hi Billy,

The SUMIF function is your friend.

First, the Pop-Up Menus in Running Log, column C

In the Summary table, formula in B2 is SUMIF(Running Log::C,A2,Running Log::B)

Fill down.

Regards,

Ian.

Reply

Answer 4

BillyCasper Author

Level 1

4 points

Oct 20, 2024 5:55 AM in response to Yellowbox

Thank you Ian for this detailed reply.. I am only a beginner when it comes to spreadsheets. so I have some questions.

The sumif option is certainly the answer and I really should get friendlier with it but,

the summary table has confused me.

I can't seem to choose between shoes when formulating, I have sumif, test value, condition, sum value.

if test value is both shoe options and condition is shoe chosen, how do I get to the drop down for my choices. I don't have the summary table..

apologies if its a dumb question

Billy

Reply

Answer 5

BillyCasper Author

Level 1

4 points

Oct 20, 2024 7:45 AM in response to Yellowbox

Thank you Ian,

I think I can see the issue.

in your scenario there are two places where the choice of shoe appears

column B

and the summary, column N

in my vast and excel like ocean of a spreadsheet (old habits) my pop up cell is column N.

you appear to have

sumif - shoe (which shoe), shoe choice, total miles

I don't have that option or don't know how to get to that option.

maybe its the way ive set it up (excel like). when I attempt to format cell O, I can't access my full shoe choice (A or B) only the one ive selected on that given day.

column N isn't formatted as such, just a text pop up menu. is this the issue

B

My head hurts

Reply

Answer 6

Yellowbox

Level 7

21,029 points

Oct 20, 2024 3:51 PM in response to BillyCasper

Hi Billy,

I am having trouble visualising your table. Please post a screenshot (command shift 4 and drag the crosshairs that appear). Click on the "Mountains and Moon" Icon below the reply box and navigate to the desktop. The screenshot will be named Screenshot with a date and time.

Regards,

Ian.

Reply

Answer 7

BillyCasper Author

Level 1

4 points

Oct 21, 2024 10:19 AM in response to Yellowbox

Hi Ian,

you are absolutely right in thinking I wanted the sum in column O for any given shoe.

I never thought of footer tables.. if that's easier...

Its a thrown together spreadsheet from around covid time so its big and cumbersome, the shoe option was a later addition.

Ignore the other shoe options, "A" was me messing around and 'P" no longer exist. The two options at the minute are the 2 shown .

680v6

1080v12

Obviously I would like to add more at a later date but those two are the ones im using.

Thank You again for your patience and helpful replies, very much appreciated

B

Reply

Answer 8

BillyCasper Author

Level 1

4 points

Oct 20, 2024 11:17 PM in response to Yellowbox

Hi Ian, here is a screen shot of the spreadsheet.

hope this helps.

B

Reply

Answer 9

Yellowbox

Level 7

21,029 points

Oct 21, 2024 9:17 PM in response to BillyCasper

I am glad to help.

Happy Numbering and keep posting in this forum!

Regards,

Ian.

Reply