Frequency function

Question

I am using Microsoft 365 on Windows 10.

I can use the Frequency function to count the number of values that fall within a group of intervals. For example, I can produce a table showing the number of invoices that have been outstanding for one week or less, more than one week but not more than two weeks, more than two weeks but not more than three weeks, etc.

This is very useful information but is it possible to extend the functionality to return something other than the count? For example, the sum or average, such as the total value of receivables outstanding in each interval rather than just the count,

IOW, despite it's name, is it possible to use "Frequency" to aggregate something other than count?

I can manually go through and sum the individual intervals but this is very tedious and inefficient. Is the only other alternative to use a macro or VBA?Or is there some other built in functionality that can accomplish what I am trying to do?

Thanks for any insights,

Answer 1

Pivot tables were made exactly for this:

In the attached (link) , two offerings:

1. On Sheet1 a plain Pivot table using the added date difference column. It groups the DaysOS.
2. On Sheet1 (2) a pivot based on a Power Query based on just the 2 columns Date and Amount.

File here: https://app.box.com/s/qqz7sbkrs435wk6nfthj680wwte77bd1

ps. it's a lot better if you create a workbook for us to look at and link to it rather than just an image of one. Most responders here wouldn't bother to set up a workbook from your picture.

Answer 2

Hi,

Enter this formula in cell F4

=sumifs($B$4:$B$18,$C$4:$C$18,"<="&D4)-sum(F$3:F3)

Hope this helps.

Answer 3

Here is a graphic that illustrates my question: is there an easier way to calculate sum by interval?

Answer 4

Hi,

Share some data and show the expected result.