Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If 3 Criteria Met
{=COUNT(IF((NewProduct2006!$C$9:$C$2000="New Prod"),(NewProduct2006!$H$7:$H$2000="In Process"),(NewProduct2006!$E$1=$A3)))} This forumal is presently giving me a 0 value. Does anyone see an issue with this formula? Thank you, Jim -- kieffer ------------------------------------------------------------------------ kieffer's Profile: http://www.excelforum.com/member.php...o&userid=30848 View this thread: http://www.excelforum.com/showthread...hreadid=533060 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If 3 Criteria Met
kieffer Wrote: {=COUNT(IF((NewProduct2006!$C$9:$C$2000="New Prod"),(NewProduct2006!$H$7:$H$2000="In Process"),(NewProduct2006!$E$1=$A3)))} This forumal is presently giving me a 0 value. Does anyone see an issue with this formula? Yes, The syntax isn't quite right and the ranges should probably be the same size. Perhaps =COUNT(IF((NewProduct2006!$C$9:$C$2000="New Prod")*(NewProduct2006!$H$9:$H$2000="In Process")*(NewProduct2006!$E$1=$A3),1)) confirmed with CTRL+SHIFT+ENTER although I'd be inclined to use SUMPRODUCT and re-arrange slightly =IF(NewProduct2006!$E$1=$A3,SUMPRODUCT(--(NewProduct2006!$C$9:$C$2000="New Prod"),--(NewProduct2006!$H$9:$H$2000="In Process")),0) only requires ENTER -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=533060 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If 3 Criteria Met
=COUNT(IF((NewProduct2006!$C$9:$C$2000="New Prod")*(NewProduct2006!$H$9:$H$2000="In Process")*(NewProduct2006!$E$1=$A3),1)) I'm still getting a 0 value. Criteria: 1. Column C contains 3 choices, in this case I want to count if it is New Prod 2. Column H contains 3 choices, in this case I want to count if it is In Process 3. $E$1 is actually a cell that contains today's date. A3 contains each day of the year. Therefore, as today's date changes, it will sum to a different row. -- kieffer ------------------------------------------------------------------------ kieffer's Profile: http://www.excelforum.com/member.php...o&userid=30848 View this thread: http://www.excelforum.com/showthread...hreadid=533060 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If 3 Criteria Met
OK, its the last part causing a problem, I don't think I understand your intent. what do you mean A3 contains each day of the year? As the formula stands you'll always get zero unless NewProduct2006!$E$1=$A3, i.e. A3 must contain today's date. Clearly that isn't what you want, are you looking at a date cell on every row? -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=533060 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If 3 Criteria Met
$E$1 value is 4/14 Date New Prod Extension Transfer 4/14 0 0 0 4/15 0 0 0 4/16 0 0 0 4/17 0 0 0 4/18 0 0 0 If the product is "In Process" and it is a "New Prod" and since today's date is 4/14, it will count in the top left. Next day will be 4/15, and $E$1 will automatically change and the data will now be summaized on the second row. -- kieffer ------------------------------------------------------------------------ kieffer's Profile: http://www.excelforum.com/member.php...o&userid=30848 View this thread: http://www.excelforum.com/showthread...hreadid=533060 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If 3 Criteria Met
OK, so you're copying the formula down a number of rows and you want it to give a count only on the row that contains today's date? In that case I don't see why it wouldn't work. Does the date match? What happens if you try without the date criteria, what do you get with =SUMPRODUCT(--(NewProduct2006!$C$9:$C$2000="New Prod"),--(NewProduct2006!$H$9:$H$2000="In Process")) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=533060 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If 3 Criteria Met
It works well without the date criteria. -- kieffer ------------------------------------------------------------------------ kieffer's Profile: http://www.excelforum.com/member.php...o&userid=30848 View this thread: http://www.excelforum.com/showthread...hreadid=533060 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If 3 Criteria Met
Then this should work.... =IF($A3=TODAY(),SUMPRODUCT(--(NewProduct2006!$C$9:$C$2000="New Prod"),--(NewProduct2006!$H$9:$H$2000="In Process")),0) ...if A3 contains today's date -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=533060 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If 3 Criteria Met
=IF($A3=TODAY(),SUMPRODUCT(--(NewProduct2006!$C$9:$C$2000="New Prod"),--(NewProduct2006!$H$9:$H$2000="In Process")),0) DLL, I think the problem here is that I will not be able to maintain historical data, only today's data. For example, when today is 4/16, 4/14 and 4/15 will 0-out. Do you agree? Date New Prod Extension Transfer 4/14 2 0 0 4/15 0 0 0 4/16 0 0 0 -- kieffer ------------------------------------------------------------------------ kieffer's Profile: http://www.excelforum.com/member.php...o&userid=30848 View this thread: http://www.excelforum.com/showthread...hreadid=533060 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count If 3 Criteria Met
kieffer Wrote: DLL, I think the problem here is that I will not be able to maintain historical data, only today's data. For example, when today is 4/16, 4/14 and 4/15 will 0-out. Do you agree? Just using a formula, yes. Unless you convert the formula result each day to a value -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=533060 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Unique Values with Multiple Criteria | Excel Worksheet Functions | |||
Total Summed Count of Numeric Repeat (Paired /Double Instance) Criteria | Excel Worksheet Functions | |||
How do I count wildcard text meeting certain criteria in EXCEL? | Excel Worksheet Functions | |||
count cell if value present in every other cell + criteria | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions |