Excel 2003 Countif multiple criteria

[asheadri]

Hello,

I am having problems creating a formula to count the number of rows that meet three seperate criteria. I want it to count every row that has "Positive" in one column, "Budgeting" in the next column, and a specific date range in the last - and return the amount of rows that meet all three of those critera. I have made a few attempts at this my latest being:

=SUMPRODUCT((Repository!$E$4:$E$65497="Positive")*(Repository!$C$4:$C$65497="Budgeting")*((COUNTIF(Repository!$F$4:$F$65497,"<"&DATE(2009,12,31)))-(COUNTIF(Repository!$F$4:$F$65497,"<"&DATE(2009,1,1)))))

But it returns 24 when it should only return 1.

Thank you very much for your help and valuable time

 

[jeffreybrown]

Hello & Welcome to the board,

How about...

=SUMPRODUCT(--(Repository!$E$4:$E$65497="Positive"),
         --(Repository!$C$4:$C$65497="Budgeting"),
         --(Repository!$F$4:$F$65497<DATE(2009,12,31)),
         --(Repository!$F$4:$F$65497>DATE(2009,1,1)))

 

[T. Valko]

Hello,

I am having problems creating a formula to count the number of rows that meet three seperate criteria. I want it to count every row that has "Positive" in one column, "Budgeting" in the next column, and a specific date range in the last - and return the amount of rows that meet all three of those critera. I have made a few attempts at this my latest being:

=SUMPRODUCT((Repository!$E$4:$E$65497="Positive")*(Repository!$C$4:$C$65497="Budgeting")*((COUNTIF(Repository!$F$4:$F$65497,"<"&DATE(2009,12,31)))-(COUNTIF(Repository!$F$4:$F$65497,"<"&DATE(2009,1,1)))))

But it returns 24 when it should only return 1.

Thank you very much for your help and valuable time

Is the date criteria any date within the year 2009?

 

[T. Valko]

Hello & Welcome to the board,

How about...

=SUMPRODUCT(--(Repository!$E$4:$E$65497="Positive"),
         --(Repository!$C$4:$C$65497="Budgeting"),
 
         --(Repository!$F$4:$F$65497<DATE(2009,12,31)),< p>          --(Repository!$F$4:$F$65497>DATE(2009,1,1)))

It's still jacked up!

Don't ya hate when that happens?

 

[jeffreybrown]

Yeah I tried to get it right, but maybe it's just Aladin who can do that setup so well

 

[T. Valko]

Yeah I tried to get it right, but maybe it's just Aladin who can do that setup so well

Well, if the date criteria is count any date within the year 2009 then you can do something like this...

...--(YEAR(range)=2009),...

It's that less than symbol that messes with you!

When in doubt add spaces characters on either side:

...--(range < whatever),...

 

[jeffreybrown]

That's why you guys have those fancy titles behind your names...thanks...

 

[asheadri]

Yes it is any date within 2009, I'll give that a try

 

[asheadri]

For some reason I am getting a NAME error, here is a screen shot:

http://livelink.encana.com/livelink/livelink.exe?func=doc.fetch&nodeid=159571749

 

[T. Valko]

Yes it is any date within 2009, I'll give that a try

OK, try it like this...

=SUMPRODUCT(--(Repository!$E$4:$E$65497="Positive"),--(Repository!$C$4:$C$65497="Budgeting"),--(YEAR(Repository!F$4:$F$65497)=2009))

I'm a big fan of using cells to hold the criteria:

• A1 = Positive
• B1 = Budgeting
• C1 = 2009

=SUMPRODUCT(--(Repository!$E$4:$E$65497=A1),--(Repository!$C$4:$C$65497=B1),--(YEAR(Repository!F$4:$F$65497)=C1))