Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula - count Nº of entries but with other filtering criteria
Hi,
I'm trying to produce a formula that will count the number of times a name appears in one column, but only when a number value in a second column is GREATER than a number value in a third column: A B C Name Passed Failed 2 Bloggs 5 3 3 Smith 4 4 4 Jones 3 4 5 Bloggs 1 2 6 Smith 5 4 7 Jones 2 2 8 Bloggs 6 1 9 Smith 4 2 10 Jones 0 4 For example, in the table above the name 'Smith' appears three times, but only twice does the number of 'Passes' exceed the number of 'Failures'. I need the formula to return me a value of '2' for 'Smith'. (I can isolate the name 'Smith' (A2:A10="Smith") but it's the rest of the formula where I specify the other filtering criteria that I'm having difficulty with). Thanks, Steve. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula - count Nº of entries but with other filtering criteria
One way:
=SUMPRODUCT(--(B2:B10C2:C10),--(A2:A10="Smith")) In article , Struggling in Sheffield wrote: Hi, I'm trying to produce a formula that will count the number of times a name appears in one column, but only when a number value in a second column is GREATER than a number value in a third column: A B C Name Passed Failed 2 Bloggs 5 3 3 Smith 4 4 4 Jones 3 4 5 Bloggs 1 2 6 Smith 5 4 7 Jones 2 2 8 Bloggs 6 1 9 Smith 4 2 10 Jones 0 4 For example, in the table above the name 'Smith' appears three times, but only twice does the number of 'Passes' exceed the number of 'Failures'. I need the formula to return me a value of '2' for 'Smith'. (I can isolate the name 'Smith' (A2:A10="Smith") but it's the rest of the formula where I specify the other filtering criteria that I'm having difficulty with). Thanks, Steve. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula - count Nº of entries but with other filtering criter
Thank you!
"JE McGimpsey" wrote: One way: =SUMPRODUCT(--(B2:B10C2:C10),--(A2:A10="Smith")) In article , Struggling in Sheffield wrote: Hi, I'm trying to produce a formula that will count the number of times a name appears in one column, but only when a number value in a second column is GREATER than a number value in a third column: A B C Name Passed Failed 2 Bloggs 5 3 3 Smith 4 4 4 Jones 3 4 5 Bloggs 1 2 6 Smith 5 4 7 Jones 2 2 8 Bloggs 6 1 9 Smith 4 2 10 Jones 0 4 For example, in the table above the name 'Smith' appears three times, but only twice does the number of 'Passes' exceed the number of 'Failures'. I need the formula to return me a value of '2' for 'Smith'. (I can isolate the name 'Smith' (A2:A10="Smith") but it's the rest of the formula where I specify the other filtering criteria that I'm having difficulty with). Thanks, Steve. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count entries based on criteria in a different column | Excel Worksheet Functions | |||
count entries based on criteria in a different column | Excel Worksheet Functions | |||
Count of entries meeting criteria | Excel Worksheet Functions | |||
count the number of cell entries after filtering | Excel Discussion (Misc queries) | |||
I Need to Count Number of Entries Based on Two Criteria | Excel Worksheet Functions |