Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUMPRODUCT to count values in separate columns
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUMPRODUCT to count values in separate columns
Hi,
=SUMPRODUCT(--(K2:K66="Yes")*--(M2:M66="True")) should do the job ... HTH Carim |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUMPRODUCT to count values in separate columns
"Carim" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUMPRODUCT to count values in separate columns
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 S Stunell wrote: "Carim" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUMPRODUCT to count values in separate columns
Spot on - thanks for your help!!!
Simon "vezerid" wrote: 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 S Stunell wrote: "Carim" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to count values in two columns | Excel Discussion (Misc queries) | |||
How do I match identical values in 2 columns and then sort? | Excel Discussion (Misc queries) | |||
How to Count Rows with defined values in multiple columns | Excel Worksheet Functions | |||
Count Unique Values | Excel Worksheet Functions | |||
Need to sum values of columns 1 - 13 and 4 - 15 | Excel Worksheet Functions |