excelexcel-formulacountifsumproduct

Count columns which are not letter or blank

I want to count the days in a month whose day or night data is not equal to 0 or letters.

A B C D E F G H
1 Monday Monday Tuesday Tuesday Wednesday Wednesday Thursday Thursday
2 Day Night Day Night Day Night Day Night
3 n/a 10 n/a 0 5 5 10 blanc

In 3rd row, if in a day, daytime or nighttime is not a letter or 0, than that day counts 1. So, the result of the above example should be 3. (monday + wednesday + thursday)

This is the long form of the formula.

=IF(OR(AND(ISNUMBER(A3)=TRUE;A3>0);AND(ISNUMBER(B3)=TRUE;B3>0));1;0)+
IF(OR(AND(ISNUMBER(C3)=TRUE;C3>0);AND(ISNUMBER(D3)=TRUE;D3>0));1;0)+
IF(OR(AND(ISNUMBER(E3)=TRUE;E3>0);AND(ISNUMBER(F3)=TRUE;F3>0));1;0)+
IF(OR(AND(ISNUMBER(G3)=TRUE;G3>0);AND(ISNUMBER(H3)=TRUE;H3>0));1;0)

I could solve with countif or sumproduct if there weren't two conditions for a day, but I couldn't count with two conditionals.

Thank you in advance!

Solution

  • Try using the following formula:

    enter image description here

    • Formula used in cell I3

    =SUM(--(UNIQUE(FILTER(A1:H1,(A3:H3<>"")*(A3:H3<>0)*(NOT(ISTEXT(A3:H3)))),1)<>""))

    Or,

    enter image description here

    • Formula used in cell I3

    =SUM(N(UNIQUE(FILTER(A1:H1,ISNUMBER(A3:H3)*(A3:H3>0)),1)<>""))

    None of the solutions returns error, if there is no non zero number:

    enter image description here

    Update:

    enter image description here

    • Formula used in cell I3

    =SUMPRODUCT(IFERROR((ISNUMBER(A3:H3)*(A3:H3>0))/
  COUNTIFS(A3:H3,">0",$A$1:$H$1,$A$1:$H$1),0))