Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I count pairs of cells when each matches a condition?
I want to count the number of instances where 2 separate conditions are each
met e.g. the number of times when column A has a value of "x" AND column B simultaneously has a value of "y" (actually one would be a numerical value and one text). I am getting confused looking at the help section for array formulae. Help?! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I count pairs of cells when each matches a condition?
You can use an array formula or just a helper column:
In an un-used column enter: =AND(A1="x",B1="y")*1 and copy down. Then just sum the contents of the new column. -- Gary's Student "Richard pile" wrote: I want to count the number of instances where 2 separate conditions are each met e.g. the number of times when column A has a value of "x" AND column B simultaneously has a value of "y" (actually one would be a numerical value and one text). I am getting confused looking at the help section for array formulae. Help?! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I count pairs of cells when each matches a condition?
For a single formula approach, try something like this:
For numbers in Col_A and text in Col_B This formula counts to number of times Col_A contains the number 1 when Col_B contains the letter "d": C1: =SUMPRODUCT((A1:A10=1)*(B1:B10="d")) Adjust range references to suit your situation. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "Richard pile" wrote: I want to count the number of instances where 2 separate conditions are each met e.g. the number of times when column A has a value of "x" AND column B simultaneously has a value of "y" (actually one would be a numerical value and one text). I am getting confused looking at the help section for array formulae. Help?! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I count pairs of cells when each matches a condition?
=sumproduct(--(a1:a10="x"),--(b1:b10=33))
Change the range--but don't use the whole column. Richard pile wrote: I want to count the number of instances where 2 separate conditions are each met e.g. the number of times when column A has a value of "x" AND column B simultaneously has a value of "y" (actually one would be a numerical value and one text). I am getting confused looking at the help section for array formulae. Help?! -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I count pairs of cells when each matches a condition?
Try this:
if X is in C1 and Y is in D1 then =SUMPRODUCT((A1:A10=C1)*(B1:B10=D1)) or =SUMPRODUCT((A1:A10=x)*(B1:B10="y")) where x = nemerical value (without quotes) and y = text (inside quotes) or vise versa HTH Jean-Guy "Richard pile" wrote: I want to count the number of instances where 2 separate conditions are each met e.g. the number of times when column A has a value of "x" AND column B simultaneously has a value of "y" (actually one would be a numerical value and one text). I am getting confused looking at the help section for array formulae. Help?! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count colored cells? | Excel Worksheet Functions | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Count cells based on date range in another column | New Users to Excel | |||
how 2 Count number of cells that have specific condition format? | Excel Worksheet Functions | |||
Count number of shaded cells | Excel Discussion (Misc queries) |