Advice needed: Comparing Committed vs Actual Donations in Excel

That is a lot but what i absolutely LOVE is that you were smart enough to ask how to set it up instead of doing something and then asking how to make it work using a setup that may be poorly thought through.  That said, you may receive various opinions on the best set up so I will put forth mine and we'll see what others say...

I would have at least 2 input tables (I have include a table 'name' in the parentheses for reference later):

a) Donor Information (t_DonorInfo) - this would include all their basic information (some sort of donorID, name, contact info, etc...) and the donation committed and schedule type

b) Donation Log (t_Log) - this would have each donation received with date, donor, amount

that said you could break a) into a couple of tables:

a1) Donor Basic Info (t_Donors)

a2) Donor Commitments (t_Commitments) - in this case you could break this apart from the Donor Info table and include year, donor, commitment amount, commitment schedule.  In this way you could make this track multiple years and track changes over the year in their committments

Then it is a matter of building the 'reports' but you now have it organized so you can FILTER the data accordingly.  For example on a 'monthly' tab you could:

a) have a table: Monthly Donor List this could be a whole list like this:

=FILTER(t_DonorInfo, t_DonorInfo[schedule]="Monthly") or if you broke the table up then =CHOOSEROWS(t_Donors, XMATCH(FILTER(t_Commitments[ID], t_Commitments[schedule]="Monthly"), t_Donors[ID]))

or just the Donor IDs (which is what you really need for the next part) like this:

=FILTER(t_DonorInfo[ID], t_DonorInfo[schedule]="Monthly") or if you broke the table up then =FILTER(t_Commitments[ID], t_Commitments[schedule]="Monthly")

either way lets call the column of Monthly contributor's IDs = mDonors

b) you can then create a donation table for the 'Monthly' contributors by filtering the Donation Log:  

=FILTER(t_Log, MMULT(--(t_Log[ID}=TRANSPOSE(mDonors)),SEQUENCE(ROWS(mDonors),,,0)))

Another option that might be easier is to add a column to the t_Log table that will automatically look up the commitment amount and schedule from the other tables:

=XLOOKUP([@ID], t_Commitments[ID], t_Commitments[Amount])

=XLOOKUP([@ID], t_Commitments[ID], t_Commitments[Schedule])

then just do a Pivot Table from this expanded t_Log table and filter by the corresponding schedule type

I hope this has been helpful and not just confusing.  If I have time I will try to append a sample file..