Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I want to know how many times 2 corresponding cells meet a conditi
I have 2 columns of numbers I would like to know how many times the value in
column A meets a certain value in column B. ex) column A column B row1 1 2 row2 1 2 row3 1 2 How many times does the value 1 in column A match the corrspond value of 2 in column B. For clarity in this example it occurs 3 times? -- Tim |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
I want to know how many times 2 corresponding cells meet a conditi
=SUMPRODUCT(--(A1:A3=1),--(B1:B3=2))
-- Regards, Peo Sjoblom http://nwexcelsolutions.com "talderman" wrote in message ... I have 2 columns of numbers I would like to know how many times the value in column A meets a certain value in column B. ex) column A column B row1 1 2 row2 1 2 row3 1 2 How many times does the value 1 in column A match the corrspond value of 2 in column B. For clarity in this example it occurs 3 times? -- Tim |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
I want to know how many times 2 corresponding cells meet a conditi
=SUMPRODUCT(--(A1:A5=1),--(B1:B5=2)) Or =SUMPRODUCT(--(A1:A5=C1),--(B1:B5=D1)) where C1=1, D1=2 "talderman" wrote: I have 2 columns of numbers I would like to know how many times the value in column A meets a certain value in column B. ex) column A column B row1 1 2 row2 1 2 row3 1 2 How many times does the value 1 in column A match the corrspond value of 2 in column B. For clarity in this example it occurs 3 times? -- Tim |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
I want to know how many times 2 corresponding cells meet a conditi
=SUMPRODUCT(--(A1:A3=1),--(B1:B3=2))
The 1 and the 2 are hardcoded into this formula, but can just as easily reference other cells with those values. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
I want to know how many times 2 corresponding cells meet a con
Thank you I appreciate it those of you who replied. What does the -- mean
I'm not familiar with those -- Tim "Dave O" wrote: =SUMPRODUCT(--(A1:A3=1),--(B1:B3=2)) The 1 and the 2 are hardcoded into this formula, but can just as easily reference other cells with those values. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
I want to know how many times 2 corresponding cells meet a con
Thanks for your help what do the -- mean in the formula
-- Tim "Toppers" wrote: =SUMPRODUCT(--(A1:A5=1),--(B1:B5=2)) Or =SUMPRODUCT(--(A1:A5=C1),--(B1:B5=D1)) where C1=1, D1=2 "talderman" wrote: I have 2 columns of numbers I would like to know how many times the value in column A meets a certain value in column B. ex) column A column B row1 1 2 row2 1 2 row3 1 2 How many times does the value 1 in column A match the corrspond value of 2 in column B. For clarity in this example it occurs 3 times? -- Tim |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
I want to know how many times 2 corresponding cells meet a con
They just convert the Boolean values TRUE or FALSE into 1 or 0 thus you can
use SUMPRODUCT's built in format as opposed to multiplying the ranges with each other. You can use 0+ or 1* or N as well -- Regards, Peo Sjoblom http://nwexcelsolutions.com "talderman" wrote in message ... Thank you I appreciate it those of you who replied. What does the -- mean I'm not familiar with those -- Tim "Dave O" wrote: =SUMPRODUCT(--(A1:A3=1),--(B1:B3=2)) The 1 and the 2 are hardcoded into this formula, but can just as easily reference other cells with those values. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill cells with times | Excel Worksheet Functions | |||
Excel 2003, which formula will count the cells that meet 2 conditi | Excel Worksheet Functions | |||
counting cells in a data range that meet 3 specific conditions | Excel Discussion (Misc queries) | |||
how do i protect cells in a shared worksheet | Excel Discussion (Misc queries) | |||
Can you limit the amount of times a calc is performed on certain cells | Excel Discussion (Misc queries) |