I have a large spreadsheet containing job information. I am trying to make a "Job Count by Month" field, and it doesn't pull correctly.
My Job Info is all located in a sheet called "3PL Jobs". I have a hidden sheet titled "Under The Hood" which contains a table of months next to their names (i.e. 'Under The Hood'!E2:E13 is a column of numbers and the F column has the corresponding "January","February", etc.
I've made a cell with a dropdown list of the months (pulling from the Under The Hood sheet) and in the cell directly to the right I want it to output the number of completed jobs.
I got this working with the dropdown month list being just a simple list of numbers and not the name, but it looks awful so I was hoping to use an index match to be able to use the name.
This is the code that worked with a list of month numbers, using I19 as the entry cell for the month number
=COUNTIFS(ARRAYFORMULA(MONTH('3PL Jobs'!C3:C)),I19,'3PL Jobs'!AR3:AR,"COMPLETE")
This is the code I've tried to include to index/match the month name (this in theory should just output the month number based on the name selected in I18, but it outputs "8" no matter what is selected)
=arrayformula(index('Under The Hood'!E2:E13,MATCH(I18,'Under The Hood'!F2:F13),1))
I'm sure I'm missing something simple, but this is driving me nuts!
to turn month name into month number all you need is:
=MONTH(I18&1)