Countblanks with IF

[Peelah]

Hi

I'm looking to create a formula to count the number of blank weeks for individuals for the remaining fiscal year; however, I don't want to count blank cells if --
1) They are exiting the company before the end of the fiscal year (it should stop counting at their contract end date only if the Column B status = "Leaver")
2) They are joining part-way through the year (it should start counting at their contract start date)

E1 - AD1: weekly dates remaining in fiscal year
A2: Individual's Name
B2: Status
C2: Start Date
D2: End Date


I tried using the below formula, but it doesn't seem to be calculating properly, and I haven't found a way to wrap-in the start date --
=countblank(E2:AD2)-if(B2,"Leaver",(AD1-D2)/7)

Attached is a file of what it looks like, and column AE at the end is the expected result.


Any help would be greatly appreciated!

[Pete_UK]

I can't read your example, but you should be able to do this with a COUNTIFS function - attach a sample Excel workbook so I can try it out before posting.

Hope this helps.

Pete

[Peelah]

Hi Pete,

Here is an example workbook (red columns is the expected result)

Cheers!

[Pete_UK]

Put this formula in cell AE2:

=COUNTIFS(E2:AD2,"",$E$1:$AD$1,">="&C2,$E$1:$AD$1,"<="&IF(B2="Leaver",D2,AD$1))

then copy down.

Hope this helps.

Pete

[Peelah]

Thanks! It works like a charm