# Counting Number of Documents in a Column based on month and other criteria



## MooseManJKT (Dec 21, 2022)

Hi everyone 👋

First of all, i'd like to say i'm happy to be part of this forum. Glad to meet you all, and I hope I can be as helpful to others here as much as I need assistance (i will try hehe).

Note:
i'm referencing rows and columns on this post to the file I made available in this link via google drive share.
Please feel free to download the .xlsx file for clarity (I hope this is allowed here 😅). I've also uploaded the excel screenshot just in case that is needed.
I can't use Mini-Sheet as this is a corporate laptop that requires admin rights to install anything.


On the the issue at hand:
I've made an "Engineering Document Submission" control sheet to help me track the various engineering documents produced and submitted to our customer. I made it this way so I can use the same template for multiple projects. The data (_*K8:Y9*_ and _*K11:Y12*_) will be used as part of a weighted progress S-Curve.


What I have so far:
Cells _*K8:Y8*_ counts the documents _within the range *G7:G36*_ that are planned to be 1st submitted within the month (using the months in *K7:Y7*) that are "*Key Documents*" (using Column D).
The formula I used is _*=COUNTIFS($G$7:$G$36;">="&K$7;$G$7:$G$36;"<"&EDATE(K$7;1);$D$7:$D$36;"Y")*_

This gives me the correct count across all months for the Key Documents-1st Approval counts. 
I use the same formula for Cells *K11:Y11* to count the documents same as above, but are "*Non-Key Documents*" (using Column E) as so
_*=COUNTIFS($G$7:$G$36;">="&K$10;$G$7:$G$36;"<"&EDATE(K$10;1);$E$7:$E$36;"N")*_


What I need next:
For the next step (approvals), there are documents that are submitted with a "For Information" status, which means that the document does not require approval from the customer.

So for cells _*K9:Y9*_, I need to count for documents _within the range *H7:H36*_ that are planned to be approved within the month (using the months in _*K7:Y7*_) that are "*Key Documents*" (using Column D), *but also count documents either with N/A in the counted colum , or documents with FI in Column F.*


What can't work:
I tried fiddling around with multiple forms of =COUNTIFS, but i don't think it works with OR criteria (i read that COUNTIFS is basically exclusively AND operator). I've also tried SUMPRODUCT or MATCH, but i think i'm using the wrong methids.


Any help from any masters in this forum is greatly appreciated. Been trying to figure this out for days. 😅

Sincerely,
Moose in Jakarta, Indonesia


----------



## MooseManJKT (Dec 21, 2022)

Finally managed to install XL2BB! Really nifty! Below is the mini-sheet:

Document Status Count.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH1ENGINEERING DOCUMENTS SUBMISSION PLAN23COPY PASTE from VDRLINPUT
"Y" or "N"INPUT dates as planned
(place "N/A" if FOR INFO)PLANNING45No.TitleKey Doc?PLANNEDSubmission Period6YNInfo?1st SubmissionApproval20222023202471Y4-Nov-2220-Nov-22Key DocumentsOctNovDecJanFebMarAprMayJunJulAugSepOctNovDecJanFebMarAprMayJunJulAugSep82YFI4-Nov-22N/A1st Submission07000000000000000000000093YFI4-Nov-22N/AApproval031000000000000000000000104YFI4-Nov-22N/ANon-KeyOctNovDecJanFebMarAprMayJunJulAugSepOctNovDecJanFebMarAprMayJunJulAugSep115Y11-Nov-2227-Nov-221st Submission023102200101000000000000126Y11-Nov-2227-Nov-22Approval005102020000000000000000137Y16-Nov-222-Dec-22148N16-Nov-222-Dec-22This is what I'm tryingIs a KEY DOC159N16-Nov-222-Dec-22to make happen forAND1610N12-Dec-2228-Dec-22Key Doc-Approval sectionStatus is "FI" AND 1ST SUBMISSION PLAN is within MONTH1711N9-Jan-2325-Jan-23above (K9:Y9)OR1812N12-Dec-2228-Dec-22APPROVAL PLAN is within MONTH1913N12-Dec-2228-Dec-222014N6-Mar-2322-Mar-232115N17-Mar-232-Apr-232216N30-Apr-2316-May-232317N30-Apr-2316-May-232418NFI30-Jul-23N/A2519NFI15-Sep-23N/A2620273128322933303431353236333734383539364037Totals7125Eng-Doc ListCell FormulasRangeFormulaH7,H11:H23H7=G7+16L7:AH7,L10:AH10L7=K7+31K8:AH8L8=COUNTIFS($G$7:$G$36,">="&L$7,$G$7:$G$36,"<"&EDATE(L$7,1),$D$7:$D$36,"Y")L9:AH9L9=COUNTIFS($H$7:$H$36,">="&L$7,$H$7:$H$36,"<"&EDATE(L$7,1),$D$7:$D$36,"Y")K9K9=COUNTIFS($H$7:$H$36,">="&K$7,$H$7:$H$36,"<"&EDATE(K$7,1),$D$7:$D$36,"Y",H7:H36,"N/A")K11:AH11K11=COUNTIFS($G$7:$G$36,">="&K$10,$G$7:$G$36,"<"&EDATE(K$10,1),$E$7:$E$36,"N")K12:AH12K12=COUNTIFS($H$7:$H$36,">="&K$10,$H$7:$H$36,"<"&EDATE(K$10,1),$E$7:$E$36,"N")D37:F37D37=COUNTA(D7:D36)Cells with Conditional FormattingCellConditionCell FormatStop If TrueK7:AH7,K10:AH10Expression=IF(K$7=#REF!;TRUE;FALSE)textNO


----------

