Count number of records based on several value of columns

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

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).

-

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.

-
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

`=(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)`

-