Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for Multiple "IF's" or maybe "COUNTIF's"???
I have a worksheet that has various cells of data...some cells contain
numbers. Those numbers span negative (0.5) through (30.0) including half numbers (21.5). Two cells over from those cells with these numbers is either a YES or a NO. I want to count how many YES's and NO's there are for a particular number. In other words a formula will be entered in a random cell asking for the doc to be searched for all (19.5), when found, each case will be analyzed by the second cell over for a YES or NO. The quantity of YES's will be shown as a total in one cell and the quantity of NO's will be shown in a cell below. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for Multiple "IF's" or maybe "COUNTIF's"???
Hey MM Lines
In my example you enter the number you look for in D1, The Range of the numbers is A1:A20 and the range for yes/no is in B1:B20 i put the following formula for "YES": =SUMPRODUCT(((A1:A20)=D1)*((B1:B20)="YES")) the formula for "NO" is the same, just with a "NO" in the end. hth Carlo On Jan 10, 8:55*am, MM Lines wrote: I have a worksheet that has various cells of data...some cells contain numbers. *Those numbers span negative (0.5) through (30.0) including half numbers (21.5). *Two cells over from those cells with these numbers is either a YES or a NO. *I want to count how many YES's and NO's there are for a particular number. *In other words a formula will be entered in a random cell asking for the doc to be searched for all (19.5), when found, each case will be analyzed by the second cell over for a YES or NO. *The quantity of YES's will be shown as a total in one cell and the quantity of NO's will be shown in a cell below. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for Multiple "IF's" or maybe "COUNTIF's"???
Hi MM,
You don't mention what your ranges are so I have just used two at random, adjust to suit your actual data. =SUMPRODUCT(--(H1:H20=19.5)*(J1:J20="yes")) HTH Martin "MM Lines" wrote in message ... I have a worksheet that has various cells of data...some cells contain numbers. Those numbers span negative (0.5) through (30.0) including half numbers (21.5). Two cells over from those cells with these numbers is either a YES or a NO. I want to count how many YES's and NO's there are for a particular number. In other words a formula will be entered in a random cell asking for the doc to be searched for all (19.5), when found, each case will be analyzed by the second cell over for a YES or NO. The quantity of YES's will be shown as a total in one cell and the quantity of NO's will be shown in a cell below. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for Multiple "IF's" or maybe "COUNTIF's"???
For YES's:
=sumproduct(--(a1:a100=19.5),--(c1:c100="Yes")) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html If you're using xl2007, you can look at =countifs() in Excel's help. MM Lines wrote: I have a worksheet that has various cells of data...some cells contain numbers. Those numbers span negative (0.5) through (30.0) including half numbers (21.5). Two cells over from those cells with these numbers is either a YES or a NO. I want to count how many YES's and NO's there are for a particular number. In other words a formula will be entered in a random cell asking for the doc to be searched for all (19.5), when found, each case will be analyzed by the second cell over for a YES or NO. The quantity of YES's will be shown as a total in one cell and the quantity of NO's will be shown in a cell below. -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for Multiple "IF's" or maybe "COUNTIF's"???
Carlo, thanks for the help...I'm almost there. Your explanation works if the
analsis is for only two columns but mine has multiple columns throughout a worksheet with the target number shown in D1 of your example. Second problem is throughout the worksheet where that D1 target number is found, the yes/no is two cells to the right, or second column over. "carlo" wrote: Hey MM Lines In my example you enter the number you look for in D1, The Range of the numbers is A1:A20 and the range for yes/no is in B1:B20 i put the following formula for "YES": =SUMPRODUCT(((A1:A20)=D1)*((B1:B20)="YES")) the formula for "NO" is the same, just with a "NO" in the end. hth Carlo On Jan 10, 8:55 am, MM Lines wrote: I have a worksheet that has various cells of data...some cells contain numbers. Those numbers span negative (0.5) through (30.0) including half numbers (21.5). Two cells over from those cells with these numbers is either a YES or a NO. I want to count how many YES's and NO's there are for a particular number. In other words a formula will be entered in a random cell asking for the doc to be searched for all (19.5), when found, each case will be analyzed by the second cell over for a YES or NO. The quantity of YES's will be shown as a total in one cell and the quantity of NO's will be shown in a cell below. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for Multiple "IF's" or maybe "COUNTIF's"???
Hi MM Lines
I am not quite sure, if i understand you exactly, but this works for me: =SUMPRODUCT(((A1:F30)=M1)*((OFFSET(A1:F30,0,2))="Y ES")) Your Target cell should not be in the Range you search, that's why i new changed it to M1. Hope that makes sense, otherwise just ask Carlo |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for Multiple "IF's" or maybe "COUNTIF's"???
Thanks again Carlos...that was perfect. I'm curious though. I understand
the formula perfectly except for the "0" in the OFFSET portion. What is that zero signifying? I assume the "2" is becuase I need to have the cell two over from my target analyzed. As a caveat to this whole thing, I have the formula now entered in for about 60 rows with $'s in front of the cell labels. Two copy and paste this easily, is there a way to just change portions of formula in mass quantities? In other words if I want to change your M1 to M3 the next two rows and then M5 for the following two rows is there some kind of paste special for just that part of the formula? "carlo" wrote: Hi MM Lines I am not quite sure, if i understand you exactly, but this works for me: =SUMPRODUCT(((A1:F30)=M1)*((OFFSET(A1:F30,0,2))="Y ES")) Your Target cell should not be in the Range you search, that's why i new changed it to M1. Hope that makes sense, otherwise just ask Carlo |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula for Multiple "IF's" or maybe "COUNTIF's"???
The syntax for OFFSET, as well as for all other Excel functions (except
DATEDIF) is available in Excel help. If you want to change the M1 reference two rows at a time, I would suggest you use OFFSET again, perhaps something along the lines of OFFSET(M$1,INT((ROW()-ROW(M$1))/2),0) -- David Biddulph "MM Lines" wrote in message ... Thanks again Carlos...that was perfect. I'm curious though. I understand the formula perfectly except for the "0" in the OFFSET portion. What is that zero signifying? I assume the "2" is becuase I need to have the cell two over from my target analyzed. As a caveat to this whole thing, I have the formula now entered in for about 60 rows with $'s in front of the cell labels. Two copy and paste this easily, is there a way to just change portions of formula in mass quantities? In other words if I want to change your M1 to M3 the next two rows and then M5 for the following two rows is there some kind of paste special for just that part of the formula? "carlo" wrote: Hi MM Lines I am not quite sure, if i understand you exactly, but this works for me: =SUMPRODUCT(((A1:F30)=M1)*((OFFSET(A1:F30,0,2))="Y ES")) Your Target cell should not be in the Range you search, that's why i new changed it to M1. Hope that makes sense, otherwise just ask Carlo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple "source" workbooks linked to single "destination" workboo | Excel Worksheet Functions | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
freeze window creates multiple "views" suffixed with ":n" | Excel Discussion (Misc queries) |