Counting values by rows in even and odd columns

[LeroyG]

Hello all,

I wish to simplify a formula that count values by rows in odd (COUNTIF((A4~C4~E4~G4~I4);"1")) and even (COUNTIF((B4~D4~F4~H4~J4);"1")) columns.

The second argument for COUNTIF ("1") is a code that references to a table to be replaced by its value: 0.5 or 0.2. I think that this part of the formula is solved.

I tried a formula like =COUNTIFS(A4:J4;"1";A4:J4;ISODD(COLUMN(A4:J4))) and …;ISEVEN(…, but COLUMN return the number of the first column of the range. So it gives me only 1.

Is there a way to count a value from A to J only in odd (and then, in even) columns?

Maybe an array with A4~C4~E4~G4~I4 for codes 1 and 2, in the Σj column, and another array with B4~D4~F4~H4~J4 for codes 3 and 4, in the Σi column.

I share a reduced sample file with the original formulas to test. This is for a school roll call with more than 25 rows, and 28 to 31 double columns, so 30 ~ for each Code (there are 5 codes for each Σ column).

Thanks in advance.

[mariosv]

Please test if a formula like =SUMPRODUCT(A4:J4="1";ISODD(COLUMN(A4:J4))) does the job, SUMPRODUCT is an array function, very flexible, so you can use other functions inside, where you cannot in others.

[LeroyG]

Thanks.

Reduced

=COUNTIF((F5~H5~J5~L5~N5~P5~R5~T5~V5~X5~Z5~AB5~AD5~AF5~AH5~AJ5~AL5~AN5~AP5~AR5~AT5~AV5~AX5~AZ5~BB5~BD5~BF5~BH5~BJ5~BL5~BN5);$imprimir.C$58)*$imprimir.D$58

to

=SUMPRODUCT(F5:BO5=$imprimir.C$58;ISEVEN(COLUMN(F5:BO5)))*$imprimir.D$58