Count Holidays, Country Wise Monthly

[kumarbohra]

Hi Frnds,
i am working on the file where in need to Count holidays for Each country for the given Month in a particular year..!! i have tried with Countifs function but its not working

Thanks & Regards
Kumar

 

[Saagar]

You need to be a bit more specific. Do you have the holiday data in the file already? If you do have data and want to count holidays with in the data, you need to explain how the data is laid out.

 

[kumarbohra]

yes , I already have the Data below is the Example

Country Holiday List
Australia 01-Mar-10
Australia 03-Mar-10
Brazil 08-Mar-10
Bulgaria 16-Mar-10
Canada 17-Mar-10
Chile 02-Apr-10


From the above data i Want an output in below format

Australia March-10 Output (in Above Case will be -2)
Brazil March-10 Output
Bulgaria March-10 Output
Canada March-10 Output
Chile April-10 Output


Regards,
Kumar

 

[vds1]

You can try this formula :

= Networkdays(Start_Date , End_Date , Holidays_Range)

 

[kumarbohra]

Thanks , but I need to Count the Holidays @ Country Level from the Said Data

Country Holiday List
Australia 01-Mar-10
Australia 03-Mar-10
Brazil 08-Mar-10
Bulgaria 16-Mar-10
Canada 17-Mar-10
Chile 02-Apr-10


From the above data i Want an output in below format

Australia March-10 Output (in Above Case will be -2)
Brazil March-10 Output
Bulgaria March-10 Output
Canada March-10 Output
Chile April-10 Output

 

[Saagar]

Here is how you can do it. I assumed that the holidays are in date format and not text:

Excel 2003<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Country</td><td style="font-weight: bold;;">Holiday List</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style=";">Jan</td><td style=";">Feb</td><td style=";">Mar</td><td style=";">Apr</td><td style=";">May</td><td style=";">Jun</td><td style=";">Jul</td><td style=";">Aug</td><td style=";">Sep</td><td style=";">Oct</td><td style=";">Nov</td><td style=";">Dec</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">Australia</td><td style="text-align: right;;">1-Mar-10</td><td style="text-align: right;;">
</td><td style=";">Australia</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;background-color: #FFFF00;;">-2</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">Australia</td><td style="text-align: right;;">2-Mar-10</td><td style="text-align: right;;">
</td><td style=";">Brazil</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">-1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">Brazil</td><td style="text-align: right;;">3-Aug-10</td><td style="text-align: right;;">
</td><td style=";">Bulgaria</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;background-color: #FFFF00;;">-1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">Bulgaria</td><td style="text-align: right;;">16-Mar-10</td><td style="text-align: right;;">
</td><td style=";">Canada</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;background-color: #FFFF00;;">-1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">Canada</td><td style="text-align: right;;">17-Mar-10</td><td style="text-align: right;;">
</td><td style=";">Chile</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;background-color: #FFFF00;;">-1</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style=";">Chile</td><td style="text-align: right;;">2-Apr-10</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr></tbody></table>

Sheet1​

Here is the formula in Cell E2. Once you put this formula, you can copy it to the remaining locations in the answer table.

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">E2</th><td style="text-align:left">=-SUMPRODUCT(--($D2=$A$2:$A$7),--(E$1=TEXT($B$2:$B$7,"mmm")))</td></tr></tbody></table></td></tr></tbody></table>
Hope this helps. Let me know if this solves your problem.

 

[kumarbohra]

Thanks a Lot , this is Helpful to me ..!!

But i have use =SUMPRODUCT((A:A=G2)*(C:C>=M2)*(C:C<=N2))

Where
G2 is Country
A Column contains list of countries
C Column Contain List of Holidays
M2 Start Date
N2 End Date

in this Formula it Takes Year Separately Eg Mar-11 & mar-10 this Both are counted as two Different..!!

Where as formula that u Proposed does not hold True on this Condition

Thanks A Lot for all Ur Support