You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

User profile for user: BillyCasper
BillyCasper Author
User level: 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
9 replies
User profile for user: Yellowbox
Yellowbox
User level: Level 7
21,197 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
User profile for user: Yellowbox
Yellowbox
User level: Level 7
21,197 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
User profile for user: BillyCasper
BillyCasper Author
User level: 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
User profile for user: BillyCasper
BillyCasper Author
User level: 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
User profile for user: BillyCasper
BillyCasper Author
User level: 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

numbers query. spreadsheet help

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.
Learn more Sign up