Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

2 Answers

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
add comment

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

=(COUNT(E3:AH3)>0)+0

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

=SUMPRODUCT((COUNTIF(OFFSET(E3:AH3,ROW(E3:AH1000)-ROW(E3),0),">0")>0)+0)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.