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

#### i_excel

##### Board Regular
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

Last edited:

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### XOR LX

##### Well-known Member
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

##### Well-known Member
Hello XOR LX,

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

#### XOR LX

##### Well-known Member
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

##### Well-known Member
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

##### Well-known Member
@James

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

Cheers

#### i_excel

##### Board Regular
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

i_excel

#### i_excel

##### Board Regular
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

##### Well-known Member
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

Last edited:

#### James006

##### Well-known Member
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 ...

Replies
4
Views
74
Replies
9
Views
155
Replies
12
Views
125
Replies
4
Views
245
Replies
2
Views
154

1,195,721
Messages
6,011,296
Members
441,600
Latest member
Ramatu

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back