using mode of payment and show due amount in quarters
In my "invoice recording" sheet I use
- monthly, quarterly, every half year, yearly and one time payment as mode of payment
Now, I want to calculate
- the sum to pay yearly (new column)
- the sum to pay each quarter (4 new columns)
Any idea :-)
Best Answer
-
It sounds like you want 4 new columns on your sheet, each, like this:
If so, these are the 4 formulas I used in each of the columns above:
Q1 Formula
=IF(MONTH(Fälligkeit@row) <= 3, [Total (Brutto jährlich)]@row)
Q2 Formula
=IF(AND(MONTH(Fälligkeit@row) >= 4, MONTH(Fälligkeit@row) <= 6), [Total (Brutto jährlich)]@row)
Q3 Formula
=IF(AND(MONTH(Fälligkeit@row) >= 7, MONTH(Fälligkeit@row) <= 9), [Total (Brutto jährlich)]@row)
Q4 Formula
=IF(MONTH(Fälligkeit@row) >= 10, [Total (Brutto jährlich)]@row)
Here's information on the MONTH function. Then you can use a Report if you want to create total SUMs from each of those columns, or use a simple SUM function in the top row of the sheet.
Cheers!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Hi everybody,
meanwhile I could solve issue no. 1 on myself - I changed the mode of payment from text to numbers.
I am still searching for a formula:
- depending on column "Fälligkeit" (invoice due date) putting total amount yearly in new column Q1 - Q4
-
Apologies, I'm not sure I quite understand what it is you're looking to do.
Are you adding together values in the same row, or are you looking to sum an entire Column, but only if the Quarter is the correct Quarter?
Is this formula in the current sheet or in a second sheet?
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Hi, Genevieve,
this little question is open for me:
I prefer if the new columns Q1 - Q4 are in the same sheet.
If the invoice is due from January 1st until March 31st the total amount should appear in column "Q1" and will be added to the others.
-
It sounds like you want 4 new columns on your sheet, each, like this:
If so, these are the 4 formulas I used in each of the columns above:
Q1 Formula
=IF(MONTH(Fälligkeit@row) <= 3, [Total (Brutto jährlich)]@row)
Q2 Formula
=IF(AND(MONTH(Fälligkeit@row) >= 4, MONTH(Fälligkeit@row) <= 6), [Total (Brutto jährlich)]@row)
Q3 Formula
=IF(AND(MONTH(Fälligkeit@row) >= 7, MONTH(Fälligkeit@row) <= 9), [Total (Brutto jährlich)]@row)
Q4 Formula
=IF(MONTH(Fälligkeit@row) >= 10, [Total (Brutto jährlich)]@row)
Here's information on the MONTH function. Then you can use a Report if you want to create total SUMs from each of those columns, or use a simple SUM function in the top row of the sheet.
Cheers!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 36 Webinars
- 7.3K Forum Archives