General

Re: Function Help! Sum an item by date and by category

brjan999 — Tue, 27 Oct 2009 15:21:24 GMT

Great. Thank you for your help. I'll play around with it. Thanks again!

-Bryan

Re: Function Help! Sum an item by date and by category

Qazi Anis — Tue, 27 Oct 2009 12:54:05 GMT

formula sum(field3[field2 = current()/../field5 and field1 = current()/../field4])

field 1 = date of first table

field2 = expense type of first table

field3 = cost of first table

field4 = date of second table

field5 = expense type of second table

field6 = cost of second table

Re: Function Help! Sum an item by date and by category

brjan999 — Mon, 26 Oct 2009 14:03:51 GMT

Hi Qazi,

Thank you for the reply. I am using repeating tables. I will see if I can note what I am doing.

1st Repeating Table
Date                             Expense Type                           Cost
10/22                           Office Supplies                          $5.00
10/22                           Meals                                       $10.00
10/22                           Auto                                         $7.00
10/22                           Meals                                       $5.00
10/23                           Auto                                         $8.00
10/23                           Office Supplies                          $10.00
10/23                           Meals                                       $4.00
10/23                           Meals                                       $5.00

2nd Repeating Table (Sums by date and only by the meals category)
Date                             Expense Type                           Cost
10/22                           Meals                                       $15.00
10/23                           Meals                                       $9.00

I have successfully built the functionality required to group by date and if it is in the meal expense, but I can't seem to create a forumla that will just sum the meal expense by date. It is adding all expenses together by date. For example, I should have $15.00 on 10/22 for meals, but because I don't know how to write the formula it will take all of the expenses on 10/22 and add them together, so it would look like I spent $27.00 on 10/22 for meals. I hope that I was able to clarify the issue. Thanks again for your help.

Here is the formula that I was using:

sum(CostA[DateA = current()/../DateA])

I just don't know how to say "Sum cost by date if it is in the meals category."

Have a great day,

Bryan

Re: Function Help! Sum an item by date and by category

Qazi Anis — Mon, 26 Oct 2009 06:57:36 GMT

hi brjan999,

can you please mention a little bit about the implementation part, whether you have used repeating tables or not, how you want categories to be summed and how you want the totals for days to be summed

Function Help! Sum an item by date and by category

brjan999 — Thu, 22 Oct 2009 14:36:04 GMT

Hi Everyone,

I am trying to sum (dollar amount) by date and category that it is in. For example, let's say I have three categories of expenses: meals, gas, office supplies, and I want to sum a total for each of these items by date. Let's say I have a (3) meal expenses on 10/21, (2) meal expenses on 10/22, (1) gas expense on 10/21, (3) office supplies purchase on 10/21, and (2) office supplies on 10/22. All I am concerned about is meals, so what I want to do is a sum formula that looks only at the meals category and sums the total dollar amount by date. Something like this:

Meals for 10/21
1. $5.00
2. $10.00
3. $15.00

Meals for 10/22
1. $8.00
2. $10.00

Date                          Expense                     Amount
10/21                         Meals                           $30.00
10/22                         Meals                           $18.00

Any help would be appreciated. Thanks again for your time.

Bryan