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.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
I have two tables. One table is a single cell table (Current Month Totals). The second table contains values broken-down by month (Monthly Totals).
I need a formula to find the current month in the header section of the table Monthly Totals and the display the current month's sum into the Current Month Totals table.
Thank you
MacBook Pro (2020 and later)
Assuming your table covers only one year so the only thing we need to look for is the month name, and assuming the months are in row 1 and the sums in row 5 like it appears in your screenshot,
Format row 1 of your monthly totals as text. All the month names must text, not date & time values.
Two possible formulas in your other table would be
=XLOOKUP(MONTHNAME(MONTH(TODAY())),Monthly Totals::$1:$1,Monthly Totals::$5:$5,"not found",0)
or
=SUMIF(Monthly Totals::$1:$1,MONTHNAME(MONTH(TODAY())),Monthly Totals::$5:$5)
Or, your table could have the month listed in cell A1
=MONTHNAME(MONTH(TODAY()))
and the formula in cell A2 or B1 would be
=XLOOKUP(A1,Monthly Totals::$1:$1,Monthly Totals::$5:$5,"not found",0)
or
=SUMIF(Monthly Totals::$1:$1,A1,Monthly Totals::$5:$5)
Assuming your table covers only one year so the only thing we need to look for is the month name, and assuming the months are in row 1 and the sums in row 5 like it appears in your screenshot,
Format row 1 of your monthly totals as text. All the month names must text, not date & time values.
Two possible formulas in your other table would be
=XLOOKUP(MONTHNAME(MONTH(TODAY())),Monthly Totals::$1:$1,Monthly Totals::$5:$5,"not found",0)
or
=SUMIF(Monthly Totals::$1:$1,MONTHNAME(MONTH(TODAY())),Monthly Totals::$5:$5)
Or, your table could have the month listed in cell A1
=MONTHNAME(MONTH(TODAY()))
and the formula in cell A2 or B1 would be
=XLOOKUP(A1,Monthly Totals::$1:$1,Monthly Totals::$5:$5,"not found",0)
or
=SUMIF(Monthly Totals::$1:$1,A1,Monthly Totals::$5:$5)
Hello and welcome to Apple Support Communities, JessA1965!
We understand you're looking for a formula to sum up your monthly totals on a Numbers spreadsheet. We'd be happy to provide information that may help you out.
The SUM formula is a basic one, however you may find more information in this page: SUM
Have a great day!
Badunit,
Thank you. All the formulas (solutions) you provided worked. I do have a question though. I'm new to Numbers and I consider myself as novice when it comes to spreadsheets. I've done some very basic functions, but it's limited to things like additions, subtractions, etc. Over the weekend I did some reading; purely by accident (a lot of trial and error) I got this to work:
LOOKUP(TODAY(),Monthly Totals::A1:L1,Monthly Totals::A$5:L$5)
I know it work today, but I'm not sure what will happen when September rolls around (I guess I'll find out in a couple days). The assumptions you made are correct (i.e., my table only covers only one and rows 1 and 5 will not change).
Is there an advantage to using LOOKUP vs XLOOKUP vs SUMIF if they all give me the desired results.
Again, thank you for your help.
That does work when your months are date&time values (not text) but I don't think it is the best way to go and it will cease to work next year unless on January 1 you re-enter the months in row 1.
When you type in just the name of the month, it is taken as the first day of the month of the current year. "August" (as a date & time value) is August 1, 2021. TODAY() is not August 1, 2021. LOOKUP is finding a result based on what it does when it cannot find what you are looking for. If no exact match is found, it tries to find the closest match that is less than the value you are looking for. So if you are looking for TODAY() (Aug 30, 2021), the closest match in your table is August 1, 2021 and it gives you a correct result. Next year, though, TODAY() will be August 30, 2022. Next year the closest match in your table will be December 1, 2021. That will be the closest match all of next year and beyond unless you re-enter the months in row 1 so they get the new year's year. And I think at some point LOOKUP will cease to consider any of them to be "close enough" to be a match.
I like XLOOKUP over LOOKUP because it allows you to specify you want an "exact match" only and what to do if it does not find one. When there is no exact match, LOOKUP will return an invalid result or an error.
SUMIF works here because there will always only be one match and the result is numeric. It is "summing" only one number. I threw it into the mix as an alternative because it is shorter. I think XLOOKUP is the better choice because it is a "lookup" function and this is a "lookup" problem.
Badunit,
Thank you for taking the time to explain the various advantages and disadvantages for each solution.
Show the sum values from one table based on the current month
