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.
