Search code examples
excelcountunique-values

Count the ones in select row of cells but skip the elevens

Example Data & Expected Results

I have a row of data and I want make a count on a specific subset of cells that contain the value of 1.

I want to ignore numbers that do not have a value of 1 even though they may contain ones such as 11, 21, 120 etc.

There are no negative values involved.

In the example provided my data subset would be:

(B3,C3,I3,J3,P3,Q3) or specifically (1,1,2,1,8,11) --> there are 3 ones.

My expected results for cell V3 would be 3 because there are 3 ones in the set of data. 11 has been ignored.

First post so link to ImgURL

Thank you.

Solution

  • You seem to be thinking of applying one COUNTIF to a complicated range.
    But you don't need to - you can apply the COUNTIF three times to simple ranges and add together: =COUNTIF(B1:C1,1)+COUNTIF(I1:J1,1)+COUNTIF(P1:Q1,1)