I'm trying to count the number of non-empty rows from a certain range. So based on the example below:

• I want to count the number of rows from TABLE1 that have "X" in the "1st" or/and "2nd" column [B3:C20] (no matter in which column it has the value, even when in both, it counts as 1, but if in none then 0);
• do this separately for Type A [F3] or Type B [F4].

I created something like this, but it considers it as an AND function instead of OR, and doesn't look for the Type A/B from the first column:

=COUNTIFS(Table1[[#All],[1st]],"<>"&"",Table1[[#All],[2nd]],"<>"&"")-1

Tried also with SUMPRODUCT, but no luck. I know it could be easily solved by adding another column that would put an "X" if in one of the columns or both are not empty, but in this scenario, I have to work with what I presented. Can anybody help me out with this?

CodePudding user response：

Edit: For any version of excel No. of X

=SUMPRODUCT(--(MMULT(--($B$3:$C$20="X"),{1;1})>0),--($A$3:$A$20=E3))

And for total-

=SUM(--($B$3:$C$20="X")*($A$3:$A$20=E3))

If you are on Microsoft-365 then could try to count No. of X

=SUM(FILTER(--BYROW($B$3:$C$20,LAMBDA(a,OR(a="X"))),$A$3:$A$20=E3))

And for total count

=SUM(--(FILTER($B$3:$C$20,$A$3:$A$20=E3)="X"))

Here

• BYROW($B$3:$C$20,LAMBDA(a,OR(a="X"))) will merge all B & C column into one as TRUE or FALSE based on X. Then -- this supress these boolean values to numbers so that we can sum these. We can also use MMULT() instead of BYROW() function for older version of excel.
• Then FILTER() will keep only values related of Type A or Type B. And then we will sum to get count.

CodePudding user response：

Kind of a tricky question, so let's figure it out. Type A/Type B has four arrangements, ① X ""; ② "" X; ③ X X; ④ "" "", now we need to find out the count of the first three arrangements, that is ①②③④ - ④.

So the formula is:

=countifs(A3:A20,"Type A")-countifs(A3:A20,"Type A",B3:B20,"",C3:C20,"")

The process is in the picture.

Hope it helps.

CodePudding user response：

Enter this formula in [F3] and copy to [F4]:

= SUM( (Table1[Type]=$E3) * ( ( (Table1[1st]="X")   (Table1[2nd]="X") ) > 0 ) )

Enter this formula in [G3] and copy to [G4]:

= SUM( (Table1[Type]=$E3) * ( (Table1[1st]="X")   (Table1[2nd]="X") ) )

Please note that as per the data provided the results are:

CodePudding user response：

I have another solution which seems little lengthy but it works fine on all versions of Excel.