# Excel 2003 COUNTIF function help



## NCTS (May 15, 2007)

Is there a built in function for Excel 2003 that will use the COUNTIF based on two criteria.

I would liek to count the number of instances for a specific string if two conditions in two different columns are met.

=COUNTIF(G1:G400,"LN") works good if I am only trying to count the numebr of values equal to "LN" but I have the need to look for the "LN" or other value and "VACANT" or "FILLED" for example which would be reflect in column H1:H400. Is this possible with any of the built-in functions?

Thanks for any help in advance.


----------



## etaf (Oct 2, 2003)

so you want one total in say column A1
which counts up all the LN in g1:g400
and also add the number of vacant or filled entries in H1:H400

so you could get an answer of 800

you could just use
=COUNTIF(G1:G400,"LN") +COUNTIF(H1:H400,"Filled") +COUNTIF(H1:H400,"vacant")


----------



## maxflia10 (Feb 25, 2003)

NCTS said:


> Is there a built in function for Excel 2003 that will use the COUNTIF based on two criteria.
> 
> I would liek to count the number of instances for a specific string if two conditions in two different columns are met.
> 
> ...


Are you wanting to count LN in column G AND Vacant or Filled in H in the same row or anywhere in G and H?


----------



## Ziggy1 (Jun 18, 2002)

try

where A1 = LN and A2 = ?

Not sure what you meant by "VACANT" or "Filled" is that the text in those fields or you mean Empty/filled


=SUMPRODUCT((G1:G400=A1)*(H1:H400=A2))


----------



## Ziggy1 (Jun 18, 2002)

well just in case here is the other scenario

Where 

A1 = LN
A2= VACANT
A3= FILLED


=SUMPRODUCT((G1:G400=A1)*SUMPRODUCT(H1:H400=A3)+(H1:H400=A2))


----------



## NCTS (May 15, 2007)

Thanks youf or yoru suggestions, however they do nto meet what i am trying to do. Let me try to explain better. I am using Excel to manage Human Resources for a one time project. I am tryign to count the total number of records that meet two criteria in two different columns. I would like to COUNT the total number of GS (which is Government Servicemen) in column H1 through H400 and also compare that against the TOTAL number of FILLED billets. For example, lets say my organization has a total of 100 "GS" billets, but we have only hired 85ht ,e remaining 15 billets are VACANT, how is it possibel to use Excel to look at all the records in one column that contain the value "GS" and also compare that in the same row to see if it is VACANT or FILLED and give me a total number. The solutions offered thus far add the total number of the values and that is not the correct sum I am lookign for. I hope this better expalins what I am trying to do and once again thanks for yoru assistance.


----------



## Ziggy1 (Jun 18, 2002)

It might be easier for you to attach a spreadsheet, these formulas count based on 2 conditions.

=SUMPRODUCT((G1:G400="gs")*(H1:H400=""))

=SUMPRODUCT((G1:G400<>"")*(H1:H400=""))

if the billet is not filled then it is VACANT and no code is filled in,right? the first formula assumes column G requires a Constant value of GS, the other one assumes column G can be filled with any value.

But either way post an example with the data as you see it and how it should be counted.


----------



## maxflia10 (Feb 25, 2003)

NCTS said:


> Thanks youf or yoru suggestions, however they do nto meet what i am trying to do. Let me try to explain better. I am using Excel to manage Human Resources for a one time project. I am tryign to count the total number of records that meet two criteria in two different columns. I would like to COUNT the total number of GS (which is Government Servicemen) in column H1 through H400 and also compare that against the TOTAL number of FILLED billets. For example, lets say my organization has a total of 100 "GS" billets, but we have only hired 85ht ,e remaining 15 billets are VACANT, how is it possibel to use Excel to look at all the records in one column that contain the value "GS" and also compare that in the same row to see if it is VACANT or FILLED and give me a total number. The solutions offered thus far add the total number of the values and that is not the correct sum I am lookign for. I hope this better expalins what I am trying to do and once again thanks for yoru assistance.


Here's my feeble attempt,

=SUMPRODUCT(--(H1:H100="GS"),--(I1:I100="VACANT))

=SUMPRODUCT(--(H1:H100="GS"),--(I1:I100="FILLED))

=SUMPRODUCT(--(ISNUMBER(MATCH(I1:I100,{"VACANT","FILLED"},0))),--(H1:H100="GS"))


----------



## ranz (May 26, 2007)

I have a field that contains the following values:

>6Hrs but <24 Hrs
1 Day to 7 Days
8 to 21 Days
> 21 Days

=COUNTIF(Complete!$V$2:$V$314,">6 Hrs but <24 Hrs")
=COUNTIF(Complete!$V$2:$V$314,"8 to 21 Days*")
=COUNTIF(Complete!$V$2:$V$314,">21 Days")

All produce the same count (which is obviously worng or why would I ask). I suspect that COUNTIF is considering the numbers in the criteria to be numeric values rather than text. 
I've also tried

=COUNTIF(Complete!$V$2:$V$314,T(">21 Days"))

Searching through the Excel help files I don't see a way to quote these numbers or force COUNTIF to treat them as text.

Any ideas? Thanks -ranz


----------



## Ziggy1 (Jun 18, 2002)

First off you should start a new post as it will confuse responses to the requestor of this thread.

if your cell contains "1 Day to 7 Days", then COUNTIF will count all those occurances

* please start a new thread and then place a link here to indicate that


----------



## ranz (May 26, 2007)

Sorry, the title "Excel 2003 COUNTIF function help" seemed apropos. Will do as you suggest though. -ranz


----------

