Forum Discussion

HansHamm's avatar
HansHamm
Qrew Assistant Captain
9 months ago

Summary Table

I am having difficulty getting my mind around this to even begin.

I need to summarize the Revenue for a project: The Revenue Date can be a single, or multiple dates, and the Revenue Amounts can vary.

Upper management wants to see this on a single form, which is no issue as I have the following: Revenue Start 1, Revenue 1, Revenue Start 2, Revenue 2, and so on.

The issue is how do I create a Summary Table that combines all the Revenue Start Dates in the correct month column and the Revenue Amounts?

The Summary example below is when we had one record (form) for each Revenue Start, and Revenue Amount.

I need the same thing when all of these dates and amounts are on a single record (form)... 

 

formulas
  • EmberKrumwied's avatar
    EmberKrumwied
    Qrew Captain
    9 months ago

    I am by far not the most experienced user here but I'll give you my thoughts.  You said that all the dates and amounts are on a single record.  If you mean that for each revenue line (from your image) those amounts are on a single record then I would ask what your field names are.

    If your field names were generic (i.e. Jan, Feb, Mar, etc.) you should also have a Year field.  So for each revenue line you would have multiple records (1 for each year) which could hold the values for each month of that year.  Then it should be pretty simple to just filter your report to the specific year you are trying to report on (i.e. 2024).

    Not sure if this helps or not.

  • HansHamm's avatar
    HansHamm
    Qrew Assistant Captain
    9 months ago

    What I am really looking for would be similar to sumif in Excel

    Something like this =SUMIF($P10:$P15,S$9,$R10:$R15)

    PQRTU
       JanFeb
    Jan1/2/2024 $       5.00117
    Jan1/9/2024 $       6.00  
    Feb2/2/2024 $       7.00  
    Apr4/2/2024 $       8.00  
    May5/2/2024 $       9.00  
    Jun6/2/2024 $    10.00