Trying to create a Countif formula that includes the # of workers allocated based on date range

zb14

I'd like to create a formula that would count all of the entries in a specific column (# of Workers Allocated) IF TODAY is within the date range that corresponds to each job entry (highlighted). As I've been tinkering with the formula, I can't quite seem to get it to return what I need. It gave me an answer of 4 (second picture), and I can't figure out why?

Thoughts?

Thank you for any help considered!

Melissa Boehl

Hello there! Have you considered using the summary instead of the body of the sheet. I have always found that when referring to a column of data, the summary works best. I am not sure what your True/False column is but I have a formula that works in the summary and can be adjusted if needed to add the sheet itself.

Also, the countif formula counts the rows that meat criteria. Is that what you are looking to do or are you looking for a total # of workers? For total workers, change to the Sumif.

See screenshots below. Hope this helps!

=COUNTIFS([Start Date]:[Start Date], <=TODAY(), [End Date]:[End Date], >=TODAY())

Melissa Boehl

Hello there! Have you considered using the summary instead of the body of the sheet. I have always found that when referring to a column of data, the summary works best. I am not sure what your True/False column is but I have a formula that works in the summary and can be adjusted if needed to add the sheet itself.

Also, the countif formula counts the rows that meat criteria. Is that what you are looking to do or are you looking for a total # of workers? For total workers, change to the Sumif.

See screenshots below. Hope this helps!

=COUNTIFS([Start Date]:[Start Date], <=TODAY(), [End Date]:[End Date], >=TODAY())

zb14

It does! Thank you. The true/false column was to help trigger the Count, but your suggestion for the SUMIF changed everything! Thank you.

Melissa Boehl

@zb14 Awesome! Please be sure to mark my answer as the answer to your question so other users can see how its resolved. Thank you!!