1. Welcome to Chandoo.org Forums. Short message for you

Hi Guest,

Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

Yours,
Chandoo
2. 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...

3. 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

Discussion in 'Ask an Excel Question' started by paulcherianc, Aug 7, 2017.

1. paulcheriancMember

Messages:
104
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.

Attached Files:

• Count if.xlsm
File size:
11.3 KB
Views:
7
Last edited: Aug 7, 2017
2. rahulshewale1Active Member

Messages:
243
Hii,

Using helper column...
Try below formula

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

Thanks.
Rahul Shewale

File size:
11.8 KB
Views:
6
3. paulcheriancMember

Messages:
104
This is not what I am looking for.
I need to exclude the helper column and integrate the formula to the count if.
4. NARAYANK991Excel Ninja

Messages:
16,619
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
paulcherianc likes this.
5. bosco_yipExcel Ninja

Messages:
1,851
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: Aug 8, 2017
paulcherianc likes this.
6. paulcheriancMember

Messages:
104
Cool ! Bravo guys! Thanks a lot!
7. paulcheriancMember

Messages:
104
Can you please explain the logic behind this formula.
8. bosco_yipExcel Ninja

Messages:
1,851
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
paulcherianc likes this.

Messages:
104
Many thanks!