# Countif formula

#### Makat

##### New Member
Hello,
I have a range of cells containing numbers 1-4 or some may have the letters "na". I am using a formula to count all the cells that contain a number and then multiply that number by another cell. Formula is:
=(COUNTIF(I5:CA5,">=0")-COUNTIF(I5:CA5,">=5"))*H5.
I have been asked not to include cell AC5 in that count and I don't know how to modify the formula to exclude that one cell.
I appreciate any help!!!! Thanks,
Makat

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Try

=((COUNTIF(I5:CA5,">=0")
- countif(AC5, ">=0")
- COUNTIF(I5:CA5,">=5")
+ countif(AC5, ">=5")) * H5

That looks like it will work!
How would I use the same concept to exclude cell AC5 if I were adding up all the numbers in a range and then dividing that number by another cell?
The current formula is: =SUM(I5:DO5)/DR5

Try
=(SUM(I5:DO5) - AC5)/DR5

=(sum(I5:DO5)-AC5)/DR5

Or another way:

That works but if cell AC5="na" then it results as #VALUE!

Thanks Dan, that solved it!
Have a great day!
Makat

Replies
11
Views
408
Replies
1
Views
134
Replies
11
Views
165
Replies
5
Views
222
Replies
2
Views
211

1,221,417
Messages
6,159,789
Members
451,589
Latest member
Harold14

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