Sum with dynamic range

[himalaya321]

Hi everyone,

I'm trying to set up a spreadsheet that will calculate the amount I will earn bi-monthly. I will be paid on the 1st and 16th day of each month. (Just because spreadsheets are fun and I like keeping track of things.)

At this stage I have:
Column A: numbers 1-365 (number of days into the contract)
Column B: dates of the 365 days (this starts from my contract start date)
Column C: the amount I expect to earn each day, based on a circular roster
Column D: =IF(OR(DAY(B2)=16,DAY(B2)=1),"Pay-day","") *This column returns "Pay-day" on the 1st and 16th of each month.
Column E: =IF(D2="Pay-day",MAX($E$2:E2)+1,E2) *This column numbers each pay-period.
Column F: =IF(D7="Pay-day",SUMIF(E:E,E7,C:C),"") *This is my expected pre-tax pay.

What I have here already does what I want it to, but I would like to know if it's possible to do this without Column E. Is there a fancy formula that I could use to sum the daily pay amount between the "Pay-day" entries in Column D?

Thanks in advance!

[oeldere]

You get better help on your question if you add an excel file, without confidential information.

Please also add the desired (expected) result (manualy) in you sheet.

[FDibbins]

Hi, welcome to the forum

Take a look at using sumifS() for this.

Something like...
=SUMIFS(C:C,B:B,"<16",B:B,">15")

[MarvinP]

Hi Himalaya and welcome to the forum,

I think you can do this problem using a Pivot Table where you group the pay sums by date. When you do this you can specify the number of days between payments. No formulas needed with Pivot Tables.
See the attached.

[himalaya321]

Thanks for the replies!

MarvinP, that's a good idea about the PivotTable. Grouping the dates like that won't work for this scenario as I'll be paid on the 1st and 16th of each month (not consistently every 14 days) but I could group by pay-period for a summarised table of payments.

I've realised that to make the sheet slightly more concise, it's Column D that isn't necessary. I've just hidden the column for now to save changing the formulas in E and F.

This is a revised version of what I have (with random amounts pasted as values).

Pay Schedule.xlsx

[MarvinP]

Hi,

By adding the Pay Period Column your work looks great to me. Good Job. Yeah for Pivot Tables.