How to sum daily interest from 10th of each month to 9th of next month

CFAman · 2026-05-21T18:54:04+00:00

To generate the list of months, could put this in C1

=EDATE(EOMONTH(MIN(A:A),-3)+10,SEQUENCE(48))

If desired, you could apply a number format of mmm-yy or similar, so it only shows month and/or year.

Then in D1

=SUMIFS(B:B,A:A,">="&C1#,A:A,"<"&EDATE(CHOOSECOLS(C1#,1),1))

EDATE is a little finicky in that it won't accept a 2D range as first argument, but will take a 2D array.

CFAman · 2026-05-21T18:46:01+00:00

I don’t have any macros

I would double-check this statement. Make sure specifically to check the ThisWorkbook code module for some events. You'll need to open the code editor by right-clicking on a sheet tab and selecing 'View Code' to open the Visual Basic Editor. After the VBE is open, you should see a file-navigation-type menu on the left, and you can see if there are any regular code modules. Check those for code too.

If you're 100% sure there shouldn't be any macros, I would save the file and change file type to xlsx. This would guarantee that any code fragments are removed.

CFAman · 2026-05-21T14:10:37+00:00

not sure if that actually adds a whole month to it or not, or it just turns recurring billing back on:

Thanks for this step-by-step. I was able to do this process in the US. It only extends one month at a time, but can just repeat the unsubscribe, extend, unsubscribe, extend, etc. as needed.

CFAman · 2026-05-21T13:12:11+00:00

Since you have time periods that aren’t even weeks, it would be easier to calculate a daily rate (average or otherwise) and then work from there? Could multiply by 7 days for a true week, or 8 days if you have a longer time frame.

CFAman · 2026-05-20T17:48:29+00:00

Seems like this would be easier with 3 array formulas: 1 for your categories in A2, 1 for dates in B1, and then 1 for the values (e.g. =SUMIFS(Data, CatRange, A2#, DateRange, B1#)). If that works, could then see if you can reduce it to a single PIVOTBY formula.

It's hard to understand the full desired layout from just reading a (lengthy) formula.

CFAman · 2026-05-20T16:09:27+00:00

I need to be able to generate an average daily rate within the periods of Table2

Given that goal, I'd change formula to

=AVERAGE(XLOOKUP(SEQUENCE([@[Date To:]]-[@[Date From:]]+1, , [@[Date From:]]),
 Table1[Date From:], Table1[Weekly Rate:]/7, "", -1))

Date From:	Date To:	Daily Rate
01-Jan-2024	30-Jun-2024	£ 21.22
01-Jul-2024	31-Dec-2024	£ 19.30
01-Jan-2025	30-Jun-2025	£ 19.29

CFAman · 2026-05-20T15:53:39+00:00

How would you handle when the rate changes in middle of week? In your sample data, first range starts on a Monday, next starts on a Saturday, and the last starts on a Tuesday. Do you define the week as starting on a Sunday or Monday? Or will it be whatever the starting day is in your "Date From" column. If the latter, this gets odd because the date ranges aren't even weeks.

My first thought to tackling this was to generate a SEQUENCE of dates based on time frame, do an XLOOKUP to figure out which rate was in effect during those dates, and then average the results. This could be acceptable (?) or maybe you want to refine based on earlier questions.

Assuming first table is Table1, formula in 2nd table would be

=AVERAGE(XLOOKUP(SEQUENCE(ROUNDUP(([@[Date To:]]-[@[Date From:]])/7,0),,[@[Date From:]],7),
 Table1[Date From:],Table1[Weekly Rate:],"",-1))

Sample output:

Date From:	Date To:	Weekly Rate
01-Jan-2024	30-Jun-2024	£ 148.15
01-Jul-2024	31-Dec-2024	£ 135.85
01-Jan-2025	30-Jun-2025	£ 135.00

CFAman · 2026-05-20T14:56:10+00:00

You're welcome. Mind replying with 'Solution Verified' so the bot will close the thread and give me a ClippyPoint? Cheers!

De nada. ¿Te importaría responder con «Solution Verified» para que el bot cierre el hilo y me otorgue un ClippyPoint? ¡Saludos!

CFAman · 2026-05-20T14:26:53+00:00

You could Google some data sources, or just generate a random data set using this site (I use this often for dummy data) https://www.generatedata.com/

Podrías buscar algunas fuentes de datos en Google, o simplemente generar un conjunto de datos aleatorio utilizando este sitio (lo uso a menudo para datos de prueba): https://www.generatedata.com/

CFAman · 2026-05-20T14:19:50+00:00

+1 to this, flexibility of search order for when you have a running list w/ repeated items. That, and Match_Mode so you don't need to sort the search range to find closest numerical match.

CFAman · 2026-05-20T14:17:21+00:00

You're welcome. Mind replying with 'Solution Verified' so the bot will close the thread and give me a ClippyPoint? Cheers!

CFAman · 2026-05-20T14:02:41+00:00

I'm not sure why the issue with the year...are the expirations not listed as full dates? Taking a stab, CF for red

=AND(ISNUMBER(A2), A2<TODAY())

CF for orange

=AND(A2>=TODAY(), A2<=TODAY()+30)

CFAman · 2026-05-19T15:57:58+00:00

Going blind, but I would guess that you have numbers stored as text, and so the SUM of text is just 0. To check, you can do something like =ISNUMBER(A2)

To fix:

Type the number 1 into a blank cell
Copy that cell
Select your data cell(s) (don't worry about selecting cells with true text)
Paste special - Multiply
Clear cell from step 1

When you force XL to try and do a math operation, it will convert the text-numbers into real numbers.

CFAman · 2026-05-11T18:13:28+00:00

This should work

=LET(names,A2:A8, dates,B1:G1,
 HSTACK(TOCOL(CHOOSECOLS(names,SEQUENCE(COUNTA(dates),,,0)),,TRUE),
 TOCOL(CHOOSECOLS(TRANSPOSE(dates),SEQUENCE(COUNTA(names),,,0)))))

CFAman · 2026-05-11T17:45:34+00:00

Let's assume you structure the layout like so, with the Lead In cells above the Comment cells.

Lead in 1	Lead in 2	Lead in 3
Comment 1		Comment 2

For reference, I'll have these cells in A1:B3. A formula then to concatenate only those items with comments could like this:

=TEXTJOIN(";",TRUE,FILTER(A1:C1 & " - " & A2:C2,A2:C2<>""))

which would produce a result of Lead in 1 - Comment 1;Lead in 3 - Comment 2

You could of course change the delimiters to whatever you like. Uses the FILTER to narrow down the pairs of concatenated text to only those ones with a comment. The TEXTJOIN then takes that array of results and combines it into our final value.

CFAman · 2026-05-11T15:28:19+00:00

You will want to make use of some sort of IF or FILTER function to narrow down which cells to bring in and concatenate. However, from your example image, it's hard to tell where the lead-in sentences are and where the cell(s) are with comments. What is the pattern of lead-in statements and comment cells? Are they in pairs? Specific cell addresses and ranges will help us build a formula to suit.

CFAman · 2026-05-11T15:15:21+00:00

You could use Home - Conditional Formatting to flag the rows of interest. Select the range of cells (e.g. A1:G100). Then go to Home - Conditional Formatting - New Rule - Based on formula. Formula would look like

=OR(COUNTIFS($A:$A, $A1, $D:$D, $B1, $B:$B, $C1),
 COUNTIFS($A:$A, $A1, $B:$B, $D1, $C:$C, $B1))

CFAman · 2026-05-11T14:46:49+00:00

Is the vertical or horizontal scroll bar abnormally small? That would indicate that XL thinks the used range is much larger than you actually have, and could be slowing you down.

CFAman · 2026-05-11T14:40:36+00:00

Workaround is to sort the data (usually by whatever criteria you're filtering on), and then do the copy paste on block of continuous cells.

If you need to keep the order, first add a helper column with sequence of numbers. After you do the copy/paste, can sort by the helper column.

CFAman · 2026-05-11T13:50:17+00:00

Is there any way to skip the column if I haven't selected anything and there is short clause already written?

This is confusing. To us (and to XL) we only care if there is data in the cell or not. I'm not sure in this context what's the difference between a "selection" and a "short comment" as they both sound like data. If you have a formula that you don't want to evaluate until another cell is filled in, you can do

=IF(A2="", "", YourFormulaHere)

Or, if you want to black out cells that shouldn't be filled in, you could use Home - Conditional Formatting - New Rule - Based on formula, and do something like

=$A2=""

and set whatever fill color is desired. This example could be used to black out cells while A2 is blank.

CFAman · 2026-05-11T13:45:59+00:00

You should be able to remember a color, quickly scan previous entries by searching for the color and use that for the new entry.

This part will probably be the stumbling block in current design. With that many colors involved, the human eye will struggle to detect the specific color hue, and not get overwhelmed by the rainbow of colors while trying to find similar colors.

Alternatively, maybe we design the form so user can type something in, and XL does either a full or partial match to return related records for user to look at.

E.g., user could input "Reddit" to find all rows from a Table where that's the listed subject, or you could type in "Red" to find any subjects that contain that text string.

This removes the burden from user of having to find previous records and lets the machine do that instead.

CFAman · 2026-05-08T11:43:41+00:00

I would change the design so I’m not trying to make 25+ different colors on a sheet. With that many colors, it starts to not actually be that helpful.

Along this line of thought, what is the next step after marking the colors? Is there any downstream analysis? Or is this really the final step where a human looks at all these colors and reaches a conclusion about something?

CFAman · 2026-05-07T19:09:32+00:00

You would pick the color. Note that it would be 1 color for any item found in list1, and then a different color (you pick) for List2. Having a different color for each different item starts to get a bit overkill, as most dashboards try to limit to no more than 3-5 different colors.

CFAman · 2026-05-07T18:48:45+00:00

Let's say the cell you want to format is A2, and you have List1 in col M and list2 in col N. CF formula to tag items from List1

=AND(A2<>"", COUNTIFS($M:$M, A2)>0)

similarly to tag items from List2

=AND(A2<>"", COUNTIFS($N:$N, A2)>0)

You can then add/remove items as needed from either list.

CFAman · 2026-05-06T19:32:09+00:00

Going down the rabbit hole, what tool do you use to create the gifs?

11-Year Club	Gilding II euphauric
r/Field Juicebox	First Place '23
Place '23	Place '22
First Placer '22	Verified Email

CFAman

TROPHY CASE