Forum Discussion

kittenmeants's avatar
kittenmeants
Brass 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 need...
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.

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
    Brass 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.

      • kittenmeants's avatar
        kittenmeants
        Brass Contributor
        Feb 02, 2023
        Thank you so much!!

Resources