# Strange Count Issue

#### criticalchain96

##### New Member
Hello, I am trying to count only values that ="1" across 3 different business divisions (A, B, and C); however, for some reason it seems to be including negative values.

=COUNTIF(K5:K8382,"=1")

I'm not sure why this is the case since it is specified to only count those that equal 1.

How do you eliminate the negative values from the count?

Thanks!

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Try

=COUNTIF(K5:K8382,1)

Why do you think it's counting negative values? Do you have other values in the range that aren't numbers?

Are you sure the values are really negative and not just formatted to display that way?

Hmm okay I think I see what is wrong - in some cases it is dividing a divisions sales (sold at a loss) by the total sales for that customer (also at a loss) and coming up as a positive amount. This is fine if it is consistent.

It seems like I'll need to do something like =COUNTIF(B8362:D8362, abs >0)

However, the syntax for the abs piece is off. Can you help with this?

You'd need SUMPRODUCT:

=SUMPRODUCT(--(ABS(B8362:D8362)>0))

You'd need SUMPRODUCT:

=SUMPRODUCT(--(ABS(B8362:D8362)>0))

Thanks! This fixed it! What is the logic behind using sum product for this?

All of the COUNTIF and SUMIF functions require a range, not an array, as the argument, and "abs>0" is not a valid expression.

Replies
3
Views
515
Replies
7
Views
414
Replies
3
Views
179
Replies
0
Views
210
Replies
5
Views
255

1,203,236
Messages
6,054,301
Members
444,715
Latest member
GlitchHawk

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