Using a sum/subtract as a condition in a countifs formula

[i_excel]

Hi

I am trying to use a summation/subtraction in a countifs formula, but it's not working out for me. I am wondering whether or not this can be done, and if so, how? Below is an example of the problem, which I hope clarifies my question:

A B C
C 1 4
G 2 3
I 3 2
C 4 1

I would like to use a formula like: countifs(a1:a4,"C", (b1:b4)-(c1:c4),>3), which for the above example should return a value of 2. The subtraction condition should only be imposed on the subtraction of values on the same row.

Let me know if I haven't explained my difficulty clearly enough and I will endeavour to rephrase the problem.

Kind regards
i_excel

 

[XOR LX]

Hi.

Not sure I understand. For neither of the rows where the column A value is C is the result of subtracting the value in column C from the value in column B greater than 3:

Row 1:

1 - 4 = -3

and -3 is certainly not greater than 3

Row 4:

4 - 1 = 3

and 3 is certainly not greater than 3.

Regards

 

[James006]

Hello XOR LX,

Is the OP thinking about an array ...ABS((B1:B4)-(C1:C4)) ...???

 

[XOR LX]

Hello XOR LX,

Is the OP thinking about an array ...ABS((B1:B4)-(C1:C4)) ...???

Ah, yes. That would make some sense...

And I suppose they could've meant to write ">=", not ">".

Wait for confirmation, I guess.

Cheers

 

[James006]

XOR LX,

You are right ...

My guess is that he is after a CSE : =SUM((A1:A4="C")*(ABS((B1:B4)-(C1:C4))>=3))

Cheers

 

[XOR LX]

@James

Perhaps, yes. You could also do that with just SUMPRODUCT - so no CSE.

Cheers

 

[i_excel]

I apologise for creating such confusion. I should have checked my example closely. For that example, b1 should instead be 8, and b4 should be 5. Now a result of 2 should make sense.

I am unable to check whether or not the solutions many of you have posted work (I am away from my computer). But I will check when I can and get back to you

Thank you for your input

i_excel

 

[i_excel]

Hi

I do not know whether or not I am using your suggested formula correctly James006 but it returns a #VALUE! error. I am unsure what you mean by array in the context - (it must be obvious I am a novice), but here are the calculations in full to clarify the matter:

In row 1, A1 is C and B1-C1>3. In row 4, the same conditions are met. Both conditions are not met in the other two rows. For this reason, the count should be 2.

I hope I have simplified my problem for you.

Kind regards
i_excel

 

[XOR LX]

In row 1, A1 is C and B1-C1>3

Forgive me, but 1-4 is most certainly not >3.

Neither is 4-1 for that matter.

Regards

 

[James006]

Hi,

Once you type your array formula ... instead of using the Enter key ...you do need to use simultaneously the three keys : Control + Shift + Enter ...

To acknowledge your input, Excel will show curly brackets at the beginning and at the end of your formula ...