Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Formula assistance

I would like to make an expenses spreadsheet, where I can enter an expense on any date, and another column will show the current average fortnightly total for that expense.


For example one expense is fuel, so I can see how much we're spending on average every fortnight (I'm paid fortnightly, so my budget is based on that).


I'm a novice in making formulas so any help here would be much appreciated!

iMac 27″ 5K, macOS 12.4

Posted on Jun 22, 2022 8:20 PM

Reply
Question marked as Apple recommended

Hi Lionel,

here an option to calculate the total cost for your expenses from the last 14 days.

Formula for D2: = SUMIFS(B,C,C2,A,"<="&$A2,A,">="&$A2−14)

It will check 3 things

  • Is the expense the same as listed in this row in column C, done with C,C2
  • Is the date <= the date shown in column A, done with A,"<="&$A2
  • Is the date >= the date shown in column A - 14 days, done with A,">="&$A2−14

I marked cell D2 to D6 in yellow because they really don't show the last 14 days, I start my list on May 1st.


Based on your region the , or the ; will be used to separate the different sections of a formula. If you write one thousand as 1,000.00 then the , is used as your formula separator. If you write one thousand as 1.000,00 then the ; is used as your formula separator.


Hope this will solve your question, please let me know if this worked for you or if something in unclear.


Ralf

Posted on Jun 22, 2022 9:36 PM

4 replies
Question marked as Apple recommended

Jun 22, 2022 9:36 PM in response to Lionel Hampton

Hi Lionel,

here an option to calculate the total cost for your expenses from the last 14 days.

Formula for D2: = SUMIFS(B,C,C2,A,"<="&$A2,A,">="&$A2−14)

It will check 3 things

  • Is the expense the same as listed in this row in column C, done with C,C2
  • Is the date <= the date shown in column A, done with A,"<="&$A2
  • Is the date >= the date shown in column A - 14 days, done with A,">="&$A2−14

I marked cell D2 to D6 in yellow because they really don't show the last 14 days, I start my list on May 1st.


Based on your region the , or the ; will be used to separate the different sections of a formula. If you write one thousand as 1,000.00 then the , is used as your formula separator. If you write one thousand as 1.000,00 then the ; is used as your formula separator.


Hope this will solve your question, please let me know if this worked for you or if something in unclear.


Ralf

Formula assistance

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.