Search code examples
excelcountifsumproduct

MS Excel: count if difference between two numbers is in a range, and a third number is within another range

Please could you help me with a tricky (in my book) Excel problem. I have a database of numbers, where I need to count how many cases there are where a subtraction of numbers falls within a range, and then a different number is within a specified range.

For instance:

    A   B   C   D   E   F
1   3   6   30      Max 20
2   1   3   21      Min 10
3   5   10  35          
4   3   9   12

Column B is always be greater than column A (they are dates).

I need to know how many rows where, for instance, B-A is between 2 and 4, and C is between the specified max and min in column F. In this example it would be 1 (row 2).

Using =SUMPRODUCT(--(B:B-A:A<=4))-SUMPRODUCT(--(B:B-A:A>=2)) I can get the first condition to work, but how do I then add the second condition?

I don't want to add any calculation rows to the database (I am trying to auto calculate from a cut/paste situation for others).

I don't (sadly) have MS365

Thank you!

Patrick

Using =SUMPRODUCT(--(B:B-A:A<=4))-SUMPRODUCT(--(B:B-A:A>=2)) I can get the first condition to work, but how do I then add the second condition?

Solution

  • You can just combine all conditions in a single SUMPRODUCT:

    =SUMPRODUCT((B:B-A:A>=2)*(B:B-A:A<=4)*(C:C>=F2)*(C:C<=F1))