Collecting Data by Month

  • Hello,


    I have a file where I capture data per month. Currently, I use a countif formula for each month, e.g., I want to know how many records I've received for the month of January. My formula is: =COUNTIFS('2021'!$A:$A,"=Chemical",'2021'!$B:$B,">44196",'2021'!$B:$B,"<44228",'2021'!$M:$M,">0",'2021'!$H:$H,"=Buffer"). This gives me all data for Chemical Buffer products that I've received in my office for the month of January 2021. Is there a way to have excel provide the data by month of January vs. greater than 12/31/20 and less than 2/1/21? (I would want this to work for all months.) I have attached the file and the formulas I've described can be found on the MFG BR Metrics and Charts tab.

  • Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the URL from the address bar in your browser) to the cross-post. We are here to help so help us help you!




    Read this to understand why we ask you to do this



    https://www.excelguru.ca/content.php?184

  • Thanks for the tips...never really thought about it, and will make sure to follow the rules. I apologize for any inconvenience I've caused. I am, however, unclear as to why I'm getting this message. I have asked for help several times with this same spreadsheet, but don't recall ever requesting help with this particular problem, either on this site, or any others. I rarely use forums other than this one as this is where I seem to get exactly what I'm looking for from you folks.


    It would be very helpful to me if you could provide me with where I have cross posted, so I may better understand what I've done wrong. Thank you!

  • Hopefully, Alan will get back to you on this.


    I've looked at your spreadsheet but there is a lot to understand. I'm not exactly sure what you are doing. Where are the summary formulas? Have you tried a PivotTable?

  • Hopefully, I can better explain what I'm looking to do. The spreadsheet is used to capture a lot of data. We are a manufacturing company, and we track chemical production records. The data is entered into the "2021" tab. The remaining tabs are used for metrics. For example, on the MFG BR Metrics and Charts tab, there is a table titled "Total Initiated by Month by Product Family". This just tells me how many production records for that product was issued for each month. The formula I use to capture the data is the COUNTIFS formula. This is the formula for January 2021:


    =COUNTIFS('2021'!$A:$A,"=Chemical",'2021'!$B:$B,">44196",'2021'!$B:$B,"<44228",'2021'!$H:$H,"=Buffer").


    February would be the same formula, but the > and < numbers would reflect January 31, 2021 and March 1, 2021, respectively, and so on for each month.


    Each year, I have to create a new spreadsheet for the following year. As you can see from the formula, I'm asking for all "Chemical" records in Column A, where Column B (from the 20201 tab) is greater than 44196 (December 31, 2020) and less than 44228 (February 1, 2020) for all Buffer products. I do the same for every month, just changing out the numbers for each month's dates.


    Currently, when I prepare the spreadsheet for the next year, I have to change all the dates (I can do this easily by find and replace throughout). However, I would like to know if there is a way to just have it capture the data for the month...."give me all the data for January, give me all data for February, etc." rather than telling it greater than December 31st but less than February 1st. This will allow me to just change the year, as well as accommodate leap years.

  • One way that might make it easier to adjust the formulas is by changing the date parameters eg, for Jan 21:


    =COUNTIFS('2021'!$A:$A,"=Chemical",'2021'!$B:$B,">="&DATE(2021,1,1),'2021'!$B:$B,"<"&DATE(2021,2,1),'2021'!$H:$H,"=Buffer")


    This way, you only need to change the year value.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!