countif formula in excel using 2 criteria

[waynerenaud]

Hi, I need to do a countif using 2 sets of criteria.
Column A has dates, Column B has either "In" or "Out"
I want to do a countif that will count the number of entries for a date that I specify and that has "In" in column B.
I have tried some variations.......
=COUNTIF(All!B:B,"In&quot gives me total number of "In"
=COUNTIF(All!A:A,C5)(C5 has the date I want to count on ) gives me total matching the date
=AND(COUNTIF(All!A:A,C5),(COUNTIF(All!B:B,"In&quot)) gives me an answer of TRUE. (Not what I was hoping for)

Is it possible to use coutif with 2 criteria.
Any Help would be appreciated

 

[DreamerZ]

How about using the Conditional Sum wizard? It's an add-in tool (go to tools, add-ins, check conditional sum wizard). Then go to tools, wizards and follow the instructions.

When I ran the wizard on a set of data the formula was:

{=SUM(IF($A$2:$A$12=DATEVALUE("12/15/2003"),IF($B$2:$B$12="In",1,0),0))}

Hope that helps.


DreamerZ
simplesolutions@prodigy.net
[ignore]

http://www.geocities.com/simplesolutions@prodigy.net

[/ignore]

 

[TheBitDoctor]

The easiest way would be to concatenate the two columns and then count the concatenated search string.

1 In 1In
2 In 2In
3 Out 3Out

=countif (C1:C3,"2In&quot


If you can't spare the additional column you'll need to write a custom function that loops through the first column for your first parameter and then increments the count only if the second column has the second parameter.

Ron

 

[TheBitDoctor]

Looks like DreamerZ found a more direct option while I was typing...

Ron

 

[Loomah]

Hi
This sould do the trick
=SUMPRODUCT((A1:A20=C1)*(B1:B20="in&quot)

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244