Countif date range and checkbox

Amy Shank

I need to count all of the contracts won & lost by month. Similarly I need to sum all of the proposal budgets won/lost by month.Does someone have a formula that would work for this?

Heather Duff

Hi Amy,

Will you be doing this in another sheet, or in the sheet summary for this sheet?

In another sheet, you could create a Month column with the month numbers (January = 1, February = 2, etc.), then:

In the count for contracts lost column:

=COUNTIF({Date Lost Range}, IFERROR(MONTH(@cell), 0) = Month@row)

In this scenario, you've named the Date Lost column as {Date Lost Range} in a cross-sheet reference. You would do the same thing for the contracts won column, except that it would be a different range.

Then in the sum for contracts lost column:

=COUNTIF({Date Lost Range}, IFERROR(MONTH(@cell), 0) = Month@row, {budget})

Where the {budget} is the named range for the budget column of your source sheet. Again, you could do the same thing for the contracts won - just use the {date won range} instead of lost.

In a sheet summary, you could create a formula for each month, where the number in bold represents the number of the month:

=COUNTIF([Date Lost]:[Date Lost], IFERROR(MONTH(@cell), 0) = 1)

=SUMIF([Date Lost]:[Date Lost], IFERROR(MONTH(@cell), 0) = 1, Budget:Budget)

Hope this helps! Let me know if you have any questions.

Best,

Heather

Amy Shank

Thank you so much Heather! The CountIf formula you suggested worked great! However, we get an unparsable error with the SumIf formula. Any suggestions? The name of the column we need to sum is "Proposed Budget".