Problem with If and sumproduct formulas

[jpruffle]

Hi,

I have a sheet that contains rows containing letter codes for example "CU". This is generated by an IF formula i.e. If true = "CU". However I need to be able to calculate the number of instances of "CU" but sumproduct will not work as it sees the cell as containing a formula not the result i.e. "CU".

Sumproduct code:

Please Login or Register  to view this content.

If code:

Please Login or Register  to view this content.

Thanks in advance

[zbor]

=countif(f5:f10;"cu")

[DonkeyOte]

zbor has given you the correct formula -- ie use COUNTIF not SUMPRODUCT
(note you will need to replace the ; with , as per your regional settings)

However, to clarify your point:

sumproduct will not work as it sees the cell as containing a formula not the result i.e. "CU"

=SUMPRODUCT(D58:D61="CU")

This is not so... the above will simply return a bunch of Boolean values (True/False), these are not numerical values thus can not be summed without coercion... you must therefore coerce the Booleans to integer, eg:

=SUMPRODUCT(--(D58:D61="CU"))

would work as here you're coercing each Boolean value (True/False) to integer equivalent (1 / 0 respectively) using the double unary operator (--)... the same could be achieved using 0+ or even 1*

To reiterate however as zbor as clearly shown a non-array COUNTIF approah will suffice...

[jpruffle]

Brilliant thank you