Count If 3 Criteria Met

K

kieffer

{=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
 
D

daddylonglegs

kieffer said:
{=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?
Click to expand...

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
 
K

kieffer

=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.
 
D

daddylonglegs

OK, its the last part causing a problem, I don't think I understand you
intent. what do you mean A3 contains each day of the year?

As the formula stands you'll always get zero unles
NewProduct2006!$E$1=$A3, i.e. A3 must contain today's date. Clearl
that isn't what you want, are you looking at a date cell on every row
 
K

kieffer

$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.
 
D

daddylonglegs

OK, so you're copying the formula down a number of rows and you want i
to give a count only on the row that contains today's date? In tha
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 ge
with

=SUMPRODUCT(--(NewProduct2006!$C$9:$C$2000="Ne
Prod"),--(NewProduct2006!$H$9:$H$2000="In Process")
 
D

daddylonglegs

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
 
K

kieffer

=IF($A3=TODAY(),SUMPRODUCT(--(NewProduct2006!$C$9:$C$2000="Ne
Prod"),--(NewProduct2006!$H$9:$H$2000="In Process")),0)

DLL, I think the problem here is that I will not be able to maintai
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
 
D

daddylonglegs

kieffer said:
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?
Click to expand...

Just using a formula, yes. Unless you convert the formula result each
day to a value
 
Top