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

 

[T. Valko]

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

I get an HTTP 405 error when I try to open that link.

 

[asheadri]

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))

Works like a charm - thank you so much!!

Andrew

 

[T. Valko]

Works like a charm - thank you so much!!

Andrew

You're welcome. Thanks for the feedback!