Sumifs Exclude Cells in Range for Certain Value

bobzee

New Member
Joined
Jan 14, 2021
Messages
3
Office Version
  1. 2011
Platform
  1. MacOS
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.
 

Some videos you may like

Excel Facts

Excel Can Read to You
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
Joined
Apr 5, 2010
Messages
63
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
Joined
Jan 14, 2021
Messages
3
Office Version
  1. 2011
Platform
  1. MacOS
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
Joined
Jan 14, 2021
Messages
3
Office Version
  1. 2011
Platform
  1. MacOS
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.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top