Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF STATEMENTS
What function can I use to count the # of times something occurs using
multiple criteria? Where 'C1' would = the number of times A1:A5 = 1500 and B1:B5 1/3/09. The answer should be 3, the # of times column A = 1500 and at the same time column B 1/3/09. A B C 1 1500 1/2/09 2 1000 1/3/09 3 1200 1/5/09 4 1500 1/5/09 5 1500 12/4/08 6 1000 1/5/09 7 1500 2/5/09 8 1500 3/5/09 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF STATEMENTS
Use SUMPRODUCT when you have multiple criteria:
=SUMPRODUCT(--(A1:A5=1500),--(B1:B5DATE(2009,1,3))) HTH Elkar "Rj" wrote: What function can I use to count the # of times something occurs using multiple criteria? Where 'C1' would = the number of times A1:A5 = 1500 and B1:B5 1/3/09. The answer should be 3, the # of times column A = 1500 and at the same time column B 1/3/09. A B C 1 1500 1/2/09 2 1000 1/3/09 3 1200 1/5/09 4 1500 1/5/09 5 1500 12/4/08 6 1000 1/5/09 7 1500 2/5/09 8 1500 3/5/09 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF STATEMENTS
Not COUNTIF but SUMPRODUCT
=SUMPRODUCT(--(A1:A20=1500),--(B1:B20DATE(2009,3,1)) I am assuming you use USA date format mm/dd/yyy; if not just reverse 1 and 3 For more details on SUMPRODUCT Bob Phillips http://www.xldynamic.com/source/xld.SUMPRODUCT.html J.E McGimpsey http://mcgimpsey.com/excel/formulae/doubleneg.html -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Rj" wrote in message ... What function can I use to count the # of times something occurs using multiple criteria? Where 'C1' would = the number of times A1:A5 = 1500 and B1:B5 1/3/09. The answer should be 3, the # of times column A = 1500 and at the same time column B 1/3/09. A B C 1 1500 1/2/09 2 1000 1/3/09 3 1200 1/5/09 4 1500 1/5/09 5 1500 12/4/08 6 1000 1/5/09 7 1500 2/5/09 8 1500 3/5/09 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use nested countif statements? | Excel Worksheet Functions | |||
INDEX / MATCH with SUMIF/COUNTIF/SUMPRODUCT Statements? | Excel Worksheet Functions | |||
IF, COUNTIF & AND STATEMENTS | Excel Discussion (Misc queries) | |||
IF Statements (Mutliple Statements) | Excel Worksheet Functions | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions |