Forum Discussion

kittenmeants's avatar
kittenmeants
Copper Contributor
Feb 01, 2023
Solved

Merging Formulas

Hello,

 

How would I combine these two formulas:

=COUNTIF(Sheet1$A:$A,"Jan") - I am trying to count all the dates that are in the Jan month column

 

My second formula is below. I believe it needs to be adjusted as currently is it only counting the cells D2 and C2. I need it to count the entire C and D columns.

=Sheet1!D2-Sheet1!C2  - I am trying to subtract two dates (the actual due date and the date completed, to get a variance number. Some projects can be submitted before the due date or after.

 

 

My tracking would look like this when completed:

MonthVariance between actual due date and date delivered
Jan 
Feb 
excel
Formulas and Functions
  • rzaneti's avatar
    rzaneti
    Feb 02, 2023

    As the formula is filtering the results by month, you can apply it to the whole columns. Here is the formula adjusted to catch the entire columns B and D.

     

    =((SUMIFS(
Sheet1!B:B, Sheet1!B:B, ">="&DATE(2023, 1, 1), Sheet1!B:B, "<="&DATE(2023, 1, 31)
))-(SUMIFS(
Sheet1!D:D, Sheet1!B:B, ">="&DATE(2023, 1, 1), Sheet1!B:B, "<="&DATE(2023, 1, 31)
)))/B2

     

    I just tested it with 400+ results for January, and it works fine:

     

    If you need anything else, let me know.

4 Replies

Sort By
  • rzaneti's avatar
    rzaneti
    Iron Contributor
    Feb 02, 2023

    Hi kittenmeants,

     

    If I understood correctly, this is a possible solution for you calculate the sum of the number of the days between the due date and the delivery date, and then find the average of this difference for the specific month. Also, I took a piece of you table that you shared in another question similar to this one.

     

    Note that this formula is based on due date, so it will include even the cases where a delivery occurred in a different month than the due date (example: due date in Feb 2nd, delivery date in Jan 30th).

     

     

    The formula inserted in D2 is the following: 

     

    =((SUMIFS(
Sheet1!B2:B3, Sheet1!B2:B3, ">="&DATE(2023, 1, 1), Sheet1!B2:B3, "<="&DATE(2023, 1, 31)
))-(SUMIFS(
Sheet1!D2:D3, Sheet1!B2:B3, ">="&DATE(2023, 1, 1), Sheet1!B2:B3, "<="&DATE(2023, 1, 31)
)))/B2

     

    If this is not what you really need, please let share some additional information about your problem.

    • kittenmeants's avatar
      kittenmeants
      Copper Contributor
      Feb 02, 2023
      Is there a way to make the formula show indefinite values? for example this is need to track the number of service requests and we could have 10 per month or 100 per month.
      • rzaneti's avatar
        rzaneti
        Iron Contributor
        Feb 02, 2023

        As the formula is filtering the results by month, you can apply it to the whole columns. Here is the formula adjusted to catch the entire columns B and D.

         

        =((SUMIFS(
Sheet1!B:B, Sheet1!B:B, ">="&DATE(2023, 1, 1), Sheet1!B:B, "<="&DATE(2023, 1, 31)
))-(SUMIFS(
Sheet1!D:D, Sheet1!B:B, ">="&DATE(2023, 1, 1), Sheet1!B:B, "<="&DATE(2023, 1, 31)
)))/B2

         

        I just tested it with 400+ results for January, and it works fine:

         

        If you need anything else, let me know.

Resources