I'm trying to help someone with an Excel spreadsheet of sales figures. I hope this explanation is clear. She has a worksheet for each category of items she sells, such as clothing, shoes, furniture, &c. Each worksheet has a column, G, for Date Sold, and the data is in Date format.
On a summary worksheet, she wants to aggregate and analyze the data, and one thing she was doing manually was counting the number of sales of each category for each month (sometimes zero).
I helped her write a formula to pull the count from the clothing worksheet, for the month of March 2019 into a cell on the summary worksheet:
=COUNTIF($CLOTHING.G3:G502;">2/2019")-COUNTIF($CLOTHING.G3:G502;">=4/2019")
This correctly yields 5. Cool. But that is just one month, and we need twelve for each year, for multiple years, and we have six categories. So we'd rather not hard-code each formula every time we need a count.
When we tried to copy the formula into cells below (for April, May, etc), hoping it would adjust the dates, it wrongly adjusted the cell range to G4:G503. The cell range shouldn't change; the months should advance, but they did not change.
We've been trying different suggestions we found online, such as naming the range G3:G502 but it didn't like that. We got an error.
We both (obviously) have a limited knowledge of Excel. Please let me know what we're doing wrong OR if there is a better way to approach this, and how to do it step by step. Please ask any questions if my explanation is not clear.
Using your formula I would adjust it as follows:
=COUNTIF($CLOTHING.G$3:G$502;">="&DATE(2019;row(1:1);1))-COUNTIF($CLOTHING.G$3:G$502;">"&EOMONTH(DATE(2019;row(1:1);1),0))
Note the addition of the $ to the cell reference. This keeps either the column or row from change when a cell is copied.
Row(1:1) will return 1 in the first cell it it entered into and as it is copied down will increase by 1. Therefore it is important not to copied it down more than 12 rows from its initial entry point.
Note the year in the date is hard coded. Alternatively the year could be placed be place in a cell and a reference to that cell could be used in place of the hard coded year. That way in subsequent years, you would just change the value in the cell instead of changing your value in the hard coded formula.
Note this solution assumes January for the first cell and months following sequentially to December for the 12th cell.
I my system is configured to use , as separators instead of ; so it is possible I may have made a mistake with those in my edits.
Alternatively you could look at COUNTIFS. It is similar to COUNTIF but requires all entries to be TRUE for an entry to be counted.
=COUNTIFS($CLOTHING.G$3:G$502;">="&DATE(2019;row(1:1);1);$CLOTHING.G$3:G$502;">"&EOMONTH(DATE(2019;row(1:1);1),0))