Excel Prorated SUMIF

Question

I have a worksheet with 2 columns, one is a dollar amount, and the other is a day of the month (1 through 31) that the dollar amount is due by (the dollars are income streams).

So, I use the following formula to SUM all the income streams due on or before a certain day:

=SUMIF(C5:C14, "<="&$B$42,B5:B14)

Column C is the due day B42 is the cell in which I input the day to compare to like "15" for "total of all income due on or before the 15th" - the idea is to have a sum of all income received for the period. Column B is the dollar amount for each income stream.

My question is:

Some of the income streams don't have a day next to them (the day cell in column C is blank). That means that that income stream doesn't come in as a check or a chunk on a certain date, it trickles in roughly evenly through out the month. So if the amount for the income stream is $10,000 and the day is 15 in a 30 day month, then I should add $5,000 to the total.

That would be something like:

=SUMIF(C5:C14, "",???)

So where the due date is blank, select ???. ??? isn't just the number, it's the number*(given_day/total_days_in_month).

So I think what I need for an accurate total is:

=SUMIF(C5:C14, "<="&$B$42,B5:B14) + SUMIF(C5:C14, "",???)

But I'm not sure how to write that exactly.

Answer 1

It depends on how you want to handle the number of days in February. You could use CHOOSE(Month($B$42),31,28,31,30,31,30,31,31,30,31,30,31) as the denominator, and DAY($B$42) as the numerator.

If you need to able to accommodate varying years where the number of days in February depends on the year in $B$42, then a more flexible approach could have a lookup table of years and the number of days in February in that year, and the 28 value in the CHOOSE formula above would instead be VLOOKUP(YEAR($B$42),MyYearLookupRange,2,false).

Answer 2

Here you go:

SUMIF(C5:C14, "",B5:B14)*$B$42/DAY(DATE(YEAR(A1),MONTH(A1)+1,0)))