Countifs based on specific months and selected criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-2110129%22%20slang%3D%22en-US%22%3ECountifs%20based%20on%20specific%20months%20and%20selected%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2110129%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Excel%20Guru's%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20count%20up%20the%20number%20of%20cells%2C%20in%20a%20column%2C%20based%20on%20dates%20and%20a%20specific%20word%3F%20I%20want%20to%20count%20up%20how%20many%20reports%20are%20ISSUED%20each%20month%2C%20starting%20in%20January%202020%20through%20until%20December%202020.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20is%20the%20simplest%20way%20to%20do%20this%3F%20I%20have%20attached%20an%20example%20of%20what%20I%20am%20trying%20to%20do.%20Maybe%20countifs%20isn't%20the%20most%20appropriate%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEddy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2110129%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2110284%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%20based%20on%20specific%20months%20and%20selected%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2110284%22%20slang%3D%22en-US%22%3EYou%20could%20also%20try%20this%20in%20H57%20and%20copy%20down.%3CBR%20%2F%3E%3CBR%20%2F%3E%3DSUMPRODUCT(--(TEXT(%24G%242%3A%24G%24255%2C%22mmyy%22)%3DTEXT(F257%2C%22mmyy%22))%2C--(%24H%242%3A%24H%24255%3D%22Issued%22))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2110266%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%20based%20on%20specific%20months%20and%20selected%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2110266%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F607700%22%20target%3D%22_blank%22%3E%40Eddy_the_Surveyor%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20table%20is%2C%20to%20be%20blunt%2C%20something%20of%20a%20mess.%20If%20there%20were%20good%20dates%20in%20the%20first%20column%20(in%20%3CU%3Eeach%3C%2FU%3E%20row)%20a%20simple%20Pivot%20Table%20could%20count%20the%20various%20actions%20by%20month.%20And%20it%20would%20do%20it%20easily.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECOUNTIF%20could%20do%20the%20same%2C%20but%20also%20would%20require%20clean%20data.%20That's%20where%20you%20need%20to%20begin.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi Excel Guru's,

 

How do I count up the number of cells, in a column, based on dates and a specific word? I want to count up how many reports are ISSUED each month, starting in January 2020 through until December 2020.

 

What is the simplest way to do this? I have attached an example of what I am trying to do. Maybe countifs isn't the most appropriate formula.

 

Any help would be greatly appreciated.

 

Thanks, 

 

Eddy

2 Replies

@Eddy_the_Surveyor 

 

Your table is, to be blunt, something of a mess. If there were good dates in the first column (in each row) a simple Pivot Table could count the various actions by month. And it would do it easily.

 

COUNTIF could do the same, but also would require clean data. That's where you need to begin.

You could also try this in H57 and copy down.

=SUMPRODUCT(--(TEXT($G$2:$G$255,"mmyy")=TEXT(F257,"mmyy")),--($H$2:$H$255="Issued"))