sumproduct and count

[RichardO]

Hi all:

I have the following sumproduct formula:

The values in column K, L, & M are as a result of a vlookup fro
another sheet. I want to count the number of times an analyst (colum
L) has a Yes in column K and has a Code in column M shows up. But I a
getting an #N/A. Do you know why?

Thanks much.

=SUMPRODUCT((L4:L19="Amanda")*(K4:K19="Yes")*(M4:M19="Code"))


Richard

 

[Peo Sjoblom]

That's because your vlookups return #N/A, change them to

=IF(ISNA(MATCH(A2,H2:H100,0)),"",VLOOKUP(A2,H2:J100,2,0))

as an example

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Harlan Grove]

...
...

The values in column K, L, & M are as a result of a vlookup from
another sheet. I want to count the number of times an analyst (column
L) has a Yes in column K and has a Code in column M shows up. But I am
getting an #N/A. Do you know why?

You likely have #N/A values in some of the cells in column K, L and M. No way to
mask them that would work with SUMPRODUCT, so you'll need an array formula.

=SUMPRODUCT((L4:L19="Amanda")*(K4:K19="Yes")*(M4:M19="Code"))

Change this to the array formula

=SUM(IF(ISTEXT(L4:L19),L4:L19="Amanda")*IF(ISTEXT(K4:K19),K4:K19="Yes")
*IF(ISTEXT(M4:M19),M4:M19="Code"))

 

[RichardO]

Thanks guys. It worked.


Richard