| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting cells in two columns that have the same results
I'm trying to come up with a formula that would give me a count of a yes/no
combination in two different columns like below: K L 1 Member Approved 2 y y 3 y n 4 n y 5 y y where I can get a count of the "Y" in column K that are also "Y" in column L, in other words, the members who are also approved. I've tried stringing two countif formulae together, but can't seem to get that to work. Any suggestions? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
counting cells in two columns that have the same results
Chris........Try this:
=SUMPRODUCT((K2:K100="Y")*(L2:L100="Y")) Adjust range references to suit your situation. Does that help? *********** Regards, Ron XL2002, WinXP "Chris" wrote: I'm trying to come up with a formula that would give me a count of a yes/no combination in two different columns like below: K L 1 Member Approved 2 y y 3 y n 4 n y 5 y y where I can get a count of the "Y" in column K that are also "Y" in column L, in other words, the members who are also approved. I've tried stringing two countif formulae together, but can't seem to get that to work. Any suggestions? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
counting cells in two columns that have the same results
Hi,
Although sumproduct is definitely a better formula to use, you cn also use an array formula (Ctrl+Shiuft+Enter) =sum(if((rangeK="y")*(rangeL="y"),1,0)) Regards, Ashish Mathur "Chris" wrote: I'm trying to come up with a formula that would give me a count of a yes/no combination in two different columns like below: K L 1 Member Approved 2 y y 3 y n 4 n y 5 y y where I can get a count of the "Y" in column K that are also "Y" in column L, in other words, the members who are also approved. I've tried stringing two countif formulae together, but can't seem to get that to work. Any suggestions? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
counting cells in two columns that have the same results
Hi Chris!
You may try this (I have placed the y-n data in A2:B5 range to test them)... =SUM(IF(A2:A5&B2:B5="yy",1,0)) enter the formula as an array formula (using CTRL+SHIFT+ENTER). You may modify the ranges as appropriate. Hope this helps! -- Thanks and kind regards "Chris" wrote: I'm trying to come up with a formula that would give me a count of a yes/no combination in two different columns like below: K L 1 Member Approved 2 y y 3 y n 4 n y 5 y y where I can get a count of the "Y" in column K that are also "Y" in column L, in other words, the members who are also approved. I've tried stringing two countif formulae together, but can't seem to get that to work. Any suggestions? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
counting cells in two columns that have the same results
I get an error that stats value not available. I copied the formula and then
simply adjusted the ranges. Does it matter if we're trying to use a "sumproduct" formula and we're dealing with text? I'm trying to evaluate two columns of text and they both have to be a "y" to be counted. Thanks. "Ron Coderre" wrote: Chris........Try this: =SUMPRODUCT((K2:K100="Y")*(L2:L100="Y")) Adjust range references to suit your situation. Does that help? *********** Regards, Ron XL2002, WinXP "Chris" wrote: I'm trying to come up with a formula that would give me a count of a yes/no combination in two different columns like below: K L 1 Member Approved 2 y y 3 y n 4 n y 5 y y where I can get a count of the "Y" in column K that are also "Y" in column L, in other words, the members who are also approved. I've tried stringing two countif formulae together, but can't seem to get that to work. Any suggestions? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
counting cells in two columns that have the same results
Tried it. Did not get an error message, but continually got a "0" answer
regardless of the combination of "Y"s and "N"s in each of the columns. "Ashish Mathur" wrote: Hi, Although sumproduct is definitely a better formula to use, you cn also use an array formula (Ctrl+Shiuft+Enter) =sum(if((rangeK="y")*(rangeL="y"),1,0)) Regards, Ashish Mathur "Chris" wrote: I'm trying to come up with a formula that would give me a count of a yes/no combination in two different columns like below: K L 1 Member Approved 2 y y 3 y n 4 n y 5 y y where I can get a count of the "Y" in column K that are also "Y" in column L, in other words, the members who are also approved. I've tried stringing two countif formulae together, but can't seem to get that to work. Any suggestions? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
counting cells in two columns that have the same results
Returns a value of 0 in every case regardless of the combinations of "Y"s and
"N"s in each of the respective columns. "RaymundCG" wrote: Hi Chris! You may try this (I have placed the y-n data in A2:B5 range to test them)... =SUM(IF(A2:A5&B2:B5="yy",1,0)) enter the formula as an array formula (using CTRL+SHIFT+ENTER). You may modify the ranges as appropriate. Hope this helps! -- Thanks and kind regards "Chris" wrote: I'm trying to come up with a formula that would give me a count of a yes/no combination in two different columns like below: K L 1 Member Approved 2 y y 3 y n 4 n y 5 y y where I can get a count of the "Y" in column K that are also "Y" in column L, in other words, the members who are also approved. I've tried stringing two countif formulae together, but can't seem to get that to work. Any suggestions? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
counting cells in two columns that have the same results
Chris:
Please post your formula so we can see what structure you're using. *********** Regards, Ron XL2002, WinXP "Chris" wrote: I get an error that stats value not available. I copied the formula and then simply adjusted the ranges. Does it matter if we're trying to use a "sumproduct" formula and we're dealing with text? I'm trying to evaluate two columns of text and they both have to be a "y" to be counted. Thanks. "Ron Coderre" wrote: Chris........Try this: =SUMPRODUCT((K2:K100="Y")*(L2:L100="Y")) Adjust range references to suit your situation. Does that help? *********** Regards, Ron XL2002, WinXP "Chris" wrote: I'm trying to come up with a formula that would give me a count of a yes/no combination in two different columns like below: K L 1 Member Approved 2 y y 3 y n 4 n y 5 y y where I can get a count of the "Y" in column K that are also "Y" in column L, in other words, the members who are also approved. I've tried stringing two countif formulae together, but can't seem to get that to work. Any suggestions? |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
counting cells in two columns that have the same results
OMG.....it works!!! Thank you, thank you, thank you! :)
"Ron Coderre" wrote: Chris........Try this: =SUMPRODUCT((K2:K100="Y")*(L2:L100="Y")) Adjust range references to suit your situation. Does that help? *********** Regards, Ron XL2002, WinXP "Chris" wrote: I'm trying to come up with a formula that would give me a count of a yes/no combination in two different columns like below: K L 1 Member Approved 2 y y 3 y n 4 n y 5 y y where I can get a count of the "Y" in column K that are also "Y" in column L, in other words, the members who are also approved. I've tried stringing two countif formulae together, but can't seem to get that to work. Any suggestions? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Linking Groups of cells between workbooks | Excel Discussion (Misc queries) | |||
| Counting blank cells until value is reached | Excel Worksheet Functions | |||
| Formula for counting number of cells | Excel Discussion (Misc queries) | |||
| abdualmohsn | Excel Discussion (Misc queries) | |||
| PLEASE HELP - Pasting Cells to WrkSheet with Hidden Columns | Excel Worksheet Functions | |||
Linear Mode
