Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional COUNTIF Function
Hi, I have two columns with different values in and I would like to count the
number of times certain values appear in the same row. i.e. Column A contains random numbers from 1-10 Column B contains random letters between A-Z How could I count the number of times the number "1" is in the same row as letter "A"?? Struggling at the moment with this so any help would be appreciated. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional COUNTIF Function
Try...
=SUMPRODUCT(--(A2:A100=1),--(B2:B100="A")) Adjust the range accordingly. Hope this helps! In article , Blair wrote: Hi, I have two columns with different values in and I would like to count the number of times certain values appear in the same row. i.e. Column A contains random numbers from 1-10 Column B contains random letters between A-Z How could I count the number of times the number "1" is in the same row as letter "A"?? Struggling at the moment with this so any help would be appreciated. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional COUNTIF Function
Got this from others much smarter than me. Let's say your random numbers are
in the range A1 to A100 and range B1 to B100 contains your letters. To determine the number of occurrences of rows with the number 1 with the number A, do this: =SUMPRODUCT(--(A1:A100=1),--(B1:B100="A")) You could set up two cells to hold the numerical and alphabetical values so that you don't have to continually modify this formula to see the results of different combinations. For example, put a number in C1 and a letter in D1 and modify the above formula to: =SUMPRODUCT(--(A1:A100=C1),--(B1:B100=D1)) "Blair" wrote: Hi, I have two columns with different values in and I would like to count the number of times certain values appear in the same row. i.e. Column A contains random numbers from 1-10 Column B contains random letters between A-Z How could I count the number of times the number "1" is in the same row as letter "A"?? Struggling at the moment with this so any help would be appreciated. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional COUNTIF Function
"Blair" wrote in message
... Hi, I have two columns with different values in and I would like to count the number of times certain values appear in the same row. i.e. Column A contains random numbers from 1-10 Column B contains random letters between A-Z How could I count the number of times the number "1" is in the same row as letter "A"?? Struggling at the moment with this so any help would be appreciated. The shortest formula: =SUMPRODUCT((A1:A10=5)*(B1:B10="B")) Bruno |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CountIF Function On Linked Spreadsheet | Excel Discussion (Misc queries) | |||
Conditional Function Application | Excel Discussion (Misc queries) | |||
can you use countif function for noncontiguous cells | Excel Worksheet Functions | |||
Countif Function -Nested | Excel Discussion (Misc queries) | |||
How do I use COUNTIF in a SUBTOTAL function to differentiate the . | Excel Worksheet Functions |