Using SUMPRODUCT to count values in separate columns

[Guest]

Hi - just wondering if anyone can help me. I have a spreadsheet and I'm
trying to get a single answer along these lines.....

If column marked renewed contains the text Yes *and* the column marked 1st
year renewal has a value of 'TRUE' then count it. i.e. :

Renewed? diff 1st year renewal?
Yes 1070 FALSE
Yes 2131 FALSE
Yes 219 TRUE
Yes 1051 FALSE
Yes 1506 FALSE
Yes 1419 FALSE
Yes 355 TRUE


Renewed shows 7 values of 'yes', 1st year renewal shows 2 true values and 5
false. I want the answer to come out as 2 with the argument 'Yes and true'
and 5 for the argument 'Yes and False'. I have been trying to adapt the
following found on this board:

=(SUMPRODUCT(--(ISNUMBER(SEARCH("yes",K2:K66))))--(SUMPRODUCT(--(ISNUMBER(SEARCH("TRUE",M2:M66))))))

but it only either seems to count one column and not the other or it adds
the values of the columns together.
Any help would be most appreciated as I think I am trying to overcomplicate
things!

Simon

 

[Carim]

Hi,

=SUMPRODUCT(--(K2:K66="Yes")*--(M2:M66="True"))

should do the job ...
HTH
Carim

 

[Guest]

Hi,

=SUMPRODUCT(--(K2:K66="Yes")*--(M2:M66="True"))

should do the job ...
HTH
Carim

Hi Carim and thanks for the swift response. I have cut and pasted this into
my spreadsheet and although a valid function it returns a value of '0'. Any
reason why this may be?
Thanks
Simon

 

[vezerid]

I think you should remove the quotes from "true". In quotes it becomes
text and
=TRUE="true"
will return FALSE

Also, if you correct the syntax (as per Carim's suggestions) in your
first formula it might work better in case some of the "yes" have a
trailing invisible space.

=SUMPRODUCT(ISNUMBER(SEARCH("yes",K2:K66))*(M2:M66=TRUE))

or

=SUMPRODUCT(ISNUMBER(SEARCH("yes",K2:K66))*M2:M6)

Multiplication of TRUE will coerce it to 1.

HTH
Kostis Vezerides

 

[Guest]

Spot on - thanks for your help!!!
Simon

I think you should remove the quotes from "true". In quotes it becomes
text and
=TRUE="true"
will return FALSE

Also, if you correct the syntax (as per Carim's suggestions) in your
first formula it might work better in case some of the "yes" have a
trailing invisible space.

=SUMPRODUCT(ISNUMBER(SEARCH("yes",K2:K66))*(M2:M66=TRUE))

or

=SUMPRODUCT(ISNUMBER(SEARCH("yes",K2:K66))*M2:M6)

Multiplication of TRUE will coerce it to 1.

HTH
Kostis Vezerides