=COUNT(IF((A1:A100="a")*(E1:E100="J"),1))

Array formula CtrlShiftEnter


"Epinn" wrote:

The following formulae yield the same results.



=SUMPRODUCT((A1:A10="a")*(E1:E10="j"))

=SUM((A1:A10="a")*(E1:E10="J")) array formula

=SUM(IF((A1:A10="a")*(E1:E10="J"),1)) array formula

=SUM(IF(A1:A10="a",IF(E1:E10="J",1))) array formula



I realize that SUM(IF(AND won't work because AND ( ) causes the entire array to be treated as one element.



I don't seem to be able to use COUNT, COUNTIF or COUNT(IF( either except the following.



Column H: =AND(A1="a",E1="j") (copied down the column). Then use the following formula:

=COUNTIF(H1:H10,TRUE)



Is it possible to use COUNT without a helper column?



I am trying to learn here. Thank you.



Epinn