Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting with conditions based on another range
HI. I've tried several different ways now to the experts :)
I have two rows of data: Emp Lvl Rating 1 4 2 3 2 2 1 4 1 2 2 3 I need to count How many emp lvl 2's with rating 2 = 1 what is the formula for this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting with conditions based on another range
Try this:
=SUMPRODUCT((A1:A6=2)*(B1:B6=1)) Hope this helps. Pete On Nov 27, 9:25*am, stephanie wrote: HI. *I've tried several different ways now to the experts :) I have two rows of data: Emp Lvl * * * * * Rating 1 * * * * * * * * * *4 2 * * * * * * * * * *3 2 * * * * * * * * * *2 1 * * * * * * * * * *4 1 * * * * * * * * * *2 2 * * * * * * * * * *3 I need to count How many emp lvl 2's with rating 2 = 1 what is the formula for this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting with conditions based on another range
Assuming your data is in columns A and B
=SUMPRODUCT((A2:A7=2)*(B2:B7=2)) "stephanie" wrote: HI. I've tried several different ways now to the experts :) I have two rows of data: Emp Lvl Rating 1 4 2 3 2 2 1 4 1 2 2 3 I need to count How many emp lvl 2's with rating 2 = 1 what is the formula for this? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting with conditions based on another range
On Nov 27, 4:25*am, stephanie
wrote: HI. *I've tried several different ways now to the experts :) I have two rows of data: Emp Lvl * * * * * Rating 1 * * * * * * * * * *4 2 * * * * * * * * * *3 2 * * * * * * * * * *2 1 * * * * * * * * * *4 1 * * * * * * * * * *2 2 * * * * * * * * * *3 I need to count How many emp lvl 2's with rating 2 = 1 what is the formula for this? I use an arrary formula: ={SUM(IF(A2:A7=1,1,0)*IF(B2:B7=2,1,0))} Create an array formula When you enter an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT +ENTER.), Microsoft Excel automatically inserts the formula between { } (braces). |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting with conditions based on another range
Thanks I understand the formula but what if I just wanted the N - how many
level 2 responded to the question or in this case received a rating (regardess of the rating score?)? -S "RonaldoOneNil" wrote: Assuming your data is in columns A and B =SUMPRODUCT((A2:A7=2)*(B2:B7=2)) "stephanie" wrote: HI. I've tried several different ways now to the experts :) I have two rows of data: Emp Lvl Rating 1 4 2 3 2 2 1 4 1 2 2 3 I need to count How many emp lvl 2's with rating 2 = 1 what is the formula for this? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting with conditions based on another range
Try this:
=COUNTIF(A:A,2) or perhaps this: =SUMPRODUCT((A1:A100=2)*(B1:B100<"")) Hope this helps. Pete On Nov 30, 8:40*am, stephanie wrote: Thanks I understand the formula but what if I just wanted the N - how many level 2 responded to the question or in this case received a rating (regardess of the rating score?)? -S "RonaldoOneNil" wrote: Assuming your data is in columns A and B =SUMPRODUCT((A2:A7=2)*(B2:B7=2)) "stephanie" wrote: HI. *I've tried several different ways now to the experts :) I have two rows of data: Emp Lvl * * * * * Rating 1 * * * * * * * * * *4 2 * * * * * * * * * *3 2 * * * * * * * * * *2 1 * * * * * * * * * *4 1 * * * * * * * * * *2 2 * * * * * * * * * *3 I need to count How many emp lvl 2's with rating 2 = 1 what is the formula for this?- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help in counting and summing cells based on multiple conditions | Excel Worksheet Functions | |||
Counting values based on multiple conditions | Excel Worksheet Functions | |||
Counting on two conditions, one with a range | Excel Worksheet Functions | |||
Counting based upon 2 conditions that are text based | Excel Discussion (Misc queries) | |||
counting based on 2 conditions | Excel Discussion (Misc queries) |