• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count IF Help

I need to have a Count If formula that will populate with some conditions. Can anyone help me to fix this?

Logic: If the date on A:A (Staff List - On Board) is not equal to EOMONTH, then populate 1st day of the month of the given date, if not, 1st day of next month)- I need to integrate this condition on the Count If formula.

Sample file attached.
 

Attachments

  • Count if.xlsm
    11.3 KB · Views: 7
Last edited:
Hii,

Using helper column...
Try below formula

=COUNTIF('Staff List - On Board'!D2:D7,B2)

Thanks.
Rahul Shewale
 

Attachments

  • Count if.xlsm
    11.8 KB · Views: 6
Hi ,

Try this in B3 and copy across :

=COUNTIFS('Staff List - On Board'!$A$2:$A$6, "<" & EOMONTH(B$2, 0), 'Staff List - On Board'!$A$2:$A$6, ">=" & B$2) + COUNTIF('Staff List - On Board'!$A$2:$A$6, EOMONTH(B$2, -1))

Narayan
 
Or, try.....

In B3, formula copy across right to C3.

=COUNTIFS('Staff List - On Board'!$A$2:$A$6,">="&EOMONTH(B$2,-1),'Staff List - On Board'!$A$2:$A$6,"<="&EOMONTH(B$2,0)-1)

Regards
Bosco
 
Last edited:
Can you please explain the logic behind this formula.
The idea is to count the number of dates fall between "End of last month" and "End of this month-1"

In C3 formula :

=COUNTIFS($A$2:$A$6,">="&EOMONTH(C2,-1),$A$2:$A$6,"<="&EOMONTH(C2,0)-1)

And, in C2 : 01/09/2017

C3 become >>

=COUNTIFS($A$2:$A$6,">=31/08/2017",$A$2:$A$6,"<=29/09/2017")

The desired result >>

=3 (The result of Countifs criteria range : 31/8/2017 to 29/9/2047)

Regards
Bosco
 
Back
Top