Sumifs Exclude Cells in Range for Certain Value

[bobzee]

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.

 

[LouisT]

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]

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]

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.