Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have excel sheet with lot of records. in each record there are several columns as it shown in the image below:

enter image description here

I want to count the number of records that do have any value in MP and OT columns. How can do that? For example the first record in the image needs NOT to be counted because all its columns are empty (do not have any value).

share|improve this question
up vote 4 down vote accepted

I'm going from memory and don't have a copy of Excel handy to check, but I think you can manage this using COUNTIFS() and treating those columns as if they were numerical. That is, checking to see if they equal zero (or are less than 1) will tell you they are null.

I'll double-check when I get to work.

share|improve this answer
It works for one cells, I want to check each cell per record and if there is non-empty && non-zero cell I need to count the record.? – Adban Nov 5 '12 at 12:33
Jamesnotjim is correct, COUNTIFS() does work with a "helper column". In a new column, use COUNTIFS() with the range set to a row, and criterion of ">0". This will return the number of non-blank/non-zero cells in that row. Repeat for each row. Total the number in this column and you have your answer. – Jonathan Garber Nov 5 '12 at 13:38
COUNTIF() counts based on one cell. COUNTIFS() counts based on as many cells as you'd like (there is, I think, an upward limit, but your sheet won't exceed it). Excel has many functions which follow this naming convention where the *S version supports multiple criteria. SUMIF() and SUMIFS() is another example. – jamesnotjim Nov 6 '12 at 5:03

For a helper column approach try this formula in AJ3 copied down


That will give you a 1 against any column with at least one number in the E:AH column range, then sum that helper column for the total number of rows with some value in the MP/OT columns

....or you can use a single formula, e.g. assuming data to row 1000 at most (adjust if required) try


share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .