Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need the correct formula
I am trying to count all the cells in a table that have a Category (first
column)of 'data' and a Priority (second column) of 'medium'. I can get a count of all the cells with a value of 'medium' by using the 'CountIf' function. But I can't seem to further restrict the count to just 'data' and 'medium'. I've tried the If-And function without sucess. Thanks in advance -- Rob K |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need the correct formula
=sumproduct(--(a1:a10="Data"),--(b1:b10="medium"))
=sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html ====== And if you're using xl2007, you may want to look at: =countifs() Rob K wrote: I am trying to count all the cells in a table that have a Category (first column)of 'data' and a Priority (second column) of 'medium'. I can get a count of all the cells with a value of 'medium' by using the 'CountIf' function. But I can't seem to further restrict the count to just 'data' and 'medium'. I've tried the If-And function without sucess. Thanks in advance -- Rob K -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need the correct formula
On 9 May, 23:30, Rob K wrote:
But I can't seem to further restrict the count to just 'data' and 'medium'. I've tried the If-And function without sucess. Thanks in advance -- Rob K Col A.........ColB Category....Priority Add ColC =A1&B1 This will concatenate the two values together. Copy this formula down. Now you can COUNTIF... using 'datamedium' HTH Andrew |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need the correct formula
=Sumproduct(--(Category="Data"),--(Priority="Medium))
where Category and Priority are the range references for your two columns. "Rob K" wrote: I am trying to count all the cells in a table that have a Category (first column)of 'data' and a Priority (second column) of 'medium'. I can get a count of all the cells with a value of 'medium' by using the 'CountIf' function. But I can't seem to further restrict the count to just 'data' and 'medium'. I've tried the If-And function without sucess. Thanks in advance -- Rob K |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need the correct formula
The concat idea worked. Thank you very much!
-- Rob K "loudfish" wrote: On 9 May, 23:30, Rob K wrote: But I can't seem to further restrict the count to just 'data' and 'medium'. I've tried the If-And function without sucess. Thanks in advance -- Rob K Col A.........ColB Category....Priority Add ColC =A1&B1 This will concatenate the two values together. Copy this formula down. Now you can COUNTIF... using 'datamedium' HTH Andrew |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please Correct the formula | Excel Discussion (Misc queries) | |||
what is the correct formula to add a column with =sum | New Users to Excel | |||
How do I correct this formula? | Excel Worksheet Functions | |||
What's the correct formula? | Excel Discussion (Misc queries) | |||
How I need correct formula, that no t rounding? | Excel Worksheet Functions |