CODEWITHSUNDEEP
excelexcel-formulapivot-table
noname738
noname738

Reputation: 61

How to retrieve specific information from a column and show its number of occurrences

I have a column which contains dates. I want to search this column and find the total entries for each month, and summarize the sum in a nice display. Example:

Pic of Data Column

I want to display the number of times 'Disposition Verification Dates' occur in each month and summarize ("count") them in a format which follows:

Pic of Summary table

I want to make several rows with the same concept in the same table for my other columns.

How do I do this? (PivotTables, VBA, IF statements?)

Upvotes: 2

Views: 93

Answers (2)

Scott Holtzman
Scott Holtzman

Reputation: 27249

No VBA needed. No helper column needed. No Pivot Table needed.

In addition to Yaegz provided, and my comments, you can use an array formula.

Assuming your data is column A and you type Jan Feb Mar ... Dec into cells B1:N1, you can enter the following formula in cell B2.

=COUNT(IF(TEXT($A$2:$A$13,"mmm")=B$1,$A$2:$A$13))

Then press Ctrl + Shift + Enter (to make it work as an array) and you will have your summary done.

Upvotes: 1

Yaegz
Yaegz

Reputation: 669

I would suggest adding a helper column which you can hide. See the formula in cell C1.

For the January column I used the following formula:

=COUNTIF($C$1:$C$7, 1)

February is:

=COUNTIF($C$1:$C$7, 2)

and so on. You can also use the YEAR function and convert the countif into a COUNTIFS function based on the other year helper column.

enter image description here

Upvotes: 1

Related Questions