Count Male and Female Monthly?

Here are two ways:

The formula in B2 of Table 2 copied right and down:

=COUNTIFS(Table 1::$A,">="&$A2,Table 1::$A,"<"&EDATE($A2,1),Table 1::$B,B$1)

Or if your region uses , instead of . as the decimal separator:

=COUNTIFS(Table 1::$A;">="&$A2;Table 1::$A;"<"&EDATE($A2;1);Table 1::$B;B$1)

The Pivot Table approach is very quick and easy.

1. Click in the table with the data.
2. From the menu choose Organize > Create Pivot Table > On Current Sheet.
3. In Pivot Options pane drag Date down to Rows, Gender down to Rows and Value to Values, and click the circled I by Value (Sum) and choose Count (All) under Summarize by:

The Pivot Table approach is quicker and easier.

If you use it you can "Refresh" the table to reflect changes in the data by going to Organize > Refresh Pivot Table.

The SUMIFS approach is better if you need a table on which you want to base a chart.

SG

justkailash wrote:

Thank you very much..... the Second way I already tried....but in this condition I won't get the remaining months which is not there....

Not sure what you mean. Pivot Tables will definitely capture all months in your data.

The advantage of the formula I have suggested is that it doesn't need an extra column. However having an extra column with the month will work fine too.

SG