Counting number of cells based on month of a date

2
votes

I am trying to count ยจ"the number of stops during each month", and then place them in another table that contain the header as the months (Data Format).

I tried many formulas, in E5:

  1. =COUNTIFS(A:A;"=GRP 1";MONTH(B:B);"=1")
  2. =COUNTIFS(A:A;"=GRP 1";MONTH(B:B);=1)
  3. =COUNTIFS(A:A;"=GRP 1";MONTH(B:B);"MONTH(E4)")

enter image description here

The error message is the following:

enter image description here

Any one has an idea?

Thanks

2
excel

2 Answers

1
votes

The criteria_range argument for the COUNTIFS function generally needs to be a range, and not an array. Hence your formula will fail.

You can use a Pivot table.

  • Drag the Group Number to the rows area
  • Drag Date of Stop to the Columns area
  • Drag Date of Stop to the Values area
  • Format as desired

enter image description here

For a formula solution, you can use SUMPRODUCT. You have to exclude the first row, or add an additional criteria as text entries will result in a #VALUE! error

E5: =SUMPRODUCT(($A$2:$A$200=$D5)*(MONTH($B$2:$B$200)=MONTH(E$4)))

Note that in your results table, the Date row are real dates formatted to show as they appear.

enter image description here

Or, if your data is in a table, you can use structured references.

=SUMPRODUCT((Table1[[Group Number]:[Group Number]]=$D5)*(MONTH(Table1[[Date of Stop]:[Date of Stop]])=MONTH(E$4)))

Edit:

As an exercise, it is possible to use whole column references, as shown in the formula below which tests to ensure that the column B entries are dates.

However, this will significantly increase calculation times, and is rather inefficient. You are much better off avoiding this by:

  • Excluding Row 1, or
  • Using a dynamic range reference, or
  • Using a Table with structured references
=SUMPRODUCT((IF(ISNUMBER(B:B),A:A,0)=$D5)*(MONTH(IF(ISNUMBER(B:B),B:B,0))=MONTH(E$4)))

Since this is an array formula, you need to "confirm" it by holding down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula as observed in the formula bar

0
votes

New code.

Think we will have to use EDATE for this formula. For cell(E5) try the following.

=COUNTIFS($A:$A,"GRP "&RIGHT($D5,1),$B:$B,">="&E$4,$B:$B,"<"&EDATE(E$4,1))

Basically EDATE allows you to get the same date (e.g 1st of the month) as many months in advance a you need. In this case, we set EDATE to 1 month in advance.