# Sumifs Exclude Cells in Range for Certain Value

#### bobzee

##### New Member
I am counting all cells in a range that are less than another cell.
I am using the formula (COUNTIF(\$V\$28:\$V\$39,"<"&V28), which works just fine and copies down nicely to subsequent rows as only the second V28 is updated thru each copy.
The problem is I want to exclude from the count any cell in the fixed range if a different cell (same row) has a certain value, let's say "AB".
i.e. if cell C28="AB", I want the formula to read (COUNTIF(\$V\$29:\$V\$39,"<"&V29).
It gets worse because, if cell C30="AB" (multiple cells could), I want the formula to include V28 & V29, skip V30, and continue from V31 thru V39.
I'm thinking as I write this that I need to use a long summation of IFs, i.e.
IF(\$C28<>"AB",COUNTIF(\$V28,"<"&V28,)) + IF(\$C28<>"AB",COUNTIF(\$V29,"<"&V28)) .... etc. Exact syntax TBD and use/nonuse of \$ also TBD. Could probably combine all the \$C28<>"AB" somehow.
Ugly, but it might work and copy down the 11 rows correctly, just incrementing the non-\$ cells.

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### LouisT

##### Board Regular
Can you provide an example of your work? It kinda sounds like you should be using "countifs" not "countif" but not 100% sure based on your description.

#### bobzee

##### New Member
Can you provide an example of your work? It kinda sounds like you should be using "countifs" not "countif" but not 100% sure based on your description.
I think you're right, but my question remains the same. Using COUNTIFS, I still want to exclude a cell from a range count if another another cell is the same row has a certain value. And the cells to be excluded from the range could be any cell, i.e. not just the first. In a range of 1 to 10 for example, I may want to exclude cells 2, 5, and 7, because another cell outside the range but in the same rows 2, 5, & 7 has a certain value.

#### bobzee

##### New Member
I am counting all cells in a range that are less than another cell.
I am using the formula (COUNTIF(\$V\$28:\$V\$39,"<"&V28), which works just fine and copies down nicely to subsequent rows as only the second V28 is updated thru each copy.
The problem is I want to exclude from the count any cell in the fixed range if a different cell (same row) has a certain value, let's say "AB".
i.e. if cell C28="AB", I want the formula to read (COUNTIF(\$V\$29:\$V\$39,"<"&V29).
It gets worse because, if cell C30="AB" (multiple cells could), I want the formula to include V28 & V29, skip V30, and continue from V31 thru V39.
I'm thinking as I write this that I need to use a long summation of IFs, i.e.
IF(\$C28<>"AB",COUNTIF(\$V28,"<"&V28,)) + IF(\$C28<>"AB",COUNTIF(\$V29,"<"&V28)) .... etc. Exact syntax TBD and use/nonuse of \$ also TBD. Could probably combine all the \$C28<>"AB" somehow.
Ugly, but it might work and copy down the 11 rows correctly, just incrementing the non-\$ cells.
I solved my problem with a kludge, ugly but works. Since I know the highest possible number in the range, if cell C28="AB", I just set the value of cell V28 to be higher than the highest possible number in the range. I used 8000, but could be any large number. That way, my (COUNTIF(\$V\$28:\$V\$39,"<"&V29) formula works and includes all Vs between 28 and 39, but effectively excludes V28 from the count because no cell can be greater than it. Not pretty, but close enough.

Replies
3
Views
79
Replies
1
Views
259
Replies
0
Views
183
Replies
4
Views
310
Replies
3
Views
107

1,127,522
Messages
5,625,305
Members
416,089
Latest member
Captkraken33

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