COUNTIFS on Table and Between Dates

  • Good Morning,

    I've conceded figuring this out myself.

    I am working on a sales forecast spreadsheet and looking for a COUNTIF that counts the number of quotes based off the service provided. Quotes are entered in the table and the quote date is in column B starting with row 5. The different services provided are headers in AJ4:AW4 with the values of the quote falling in the subsequent rows. I can get the first formula to work and it will count all the cells in the specific row but I cannot get the second formula to work to narrow down between two dates (e.g. dates for the last four months, dates for the next four months). Any help is appreciated.

    Some explanations:

    ServiceProvided is a dropdown

    This works for a total list of quotes within the requested column: =COUNTIF(INDEX('Forecast Details'!$AJ$4:$AW$500,0,MATCH(ServiceProvided,'Forecast Details'!$AJ$4:$AW$4,0)),">0")

    When I add a date "sandwich" formula (which works on other formulas within the same spreadsheet) to the COUNTIF above and make it a COUNTIFS it does not work.

    =COUNTIFS(INDEX('Forecast Details'!$AJ$4:$AW$500,0,MATCH(ServiceProvided,'Forecast Details'!$AJ$4:$AW$4,0)),">0",'Forecast Details'!$B:$B,">="&EOMONTH($M$2,-1)+1,'Forecast Details'!$B:$B,"<="&EOMONTH($M$2,3))

    See attached worksheet. First two tabs are the ones I'm working with. The cell that is in contention is on the Dashboard worksheet cell R6 and will replace the "ZZZ" in the formula in Q6 so that when the Service Provided dropdown in N2 is changed it will update the total count of quotes based off the month in M2. My next step will be to update the quote amount Column O but I think that if the quote count can be figured out then I can update the SUMIFS formula off the solution to this challenge.



Participate now!

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