Countifs in Excel 2000? Alternative solution sought!!!
 

I am sorry but I am still not sure. I know that I need to do a sumproduct but
I am unsure of how to set it up. When I select sumproduct it gives me the
option of three arrays. I am guessing that I need to select the fields that I
want it to check but I am unsure of how I tell it what to look for.

Lisa

"Gary Brown" wrote:

You want to use the SumProduct( ) function.
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Lisa" wrote:

Dear All,

I have designed an excel database in Excel 2007, but then tried to implement
it at work only to find that the countifs function doesnt work in excel 2000.
The two columns I am trying to count have pick lists, not sure if this makes
a difference. The formula in 2007 looks like as follows
=COUNTIFS(DurhamVacs!G25:G1582,"apr",DurhamVacs!N2 5:N1582,"advertised")

What I am trying to achive is, a formula that counts where one field = Apr
and another field = advertised e.g. number of vacancies advertised in April.
I hope this make sense

Lisa

Countifs in Excel 2000? Alternative solution sought!!!
 

Explain what ranges you need to test and for what criteria/condition.

Here's a small example...

......A.....B.....C
1...c......c......d
2...a......c......a
3...c......c......d
4...c......c......a
5...a......a......c

Suppose you want to count the number of instances whe

Col A = c
Col B = c
Col C = d

=SUMPRODUCT(--(A1:A5="c"),--(B1:B5="c"),--(C1:C5="d"))

Result = 2

--
Biff
Microsoft Excel MVP


"Lisa" wrote in message
...
I am sorry but I am still not sure. I know that I need to do a sumproduct
but
I am unsure of how to set it up. When I select sumproduct it gives me the
option of three arrays. I am guessing that I need to select the fields
that I
want it to check but I am unsure of how I tell it what to look for.

Lisa

"Gary Brown" wrote:

You want to use the SumProduct( ) function.
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Lisa" wrote:

Dear All,

I have designed an excel database in Excel 2007, but then tried to
implement
it at work only to find that the countifs function doesnt work in excel
2000.
The two columns I am trying to count have pick lists, not sure if this
makes
a difference. The formula in 2007 looks like as follows
=COUNTIFS(DurhamVacs!G25:G1582,"apr",DurhamVacs!N2 5:N1582,"advertised")

What I am trying to achive is, a formula that counts where one field =
Apr
and another field = advertised e.g. number of vacancies advertised in
April.
I hope this make sense

Lisa

Countifs in Excel 2000? Alternative solution sought!!!
 

Check out Bob Phillips' site for info and examples of SUMPRODUCT

http://www.xldynamic.com/source/xld.SUMPRODUCT.html


Gord Dibben MS Excel MVP

On Mon, 29 Mar 2010 14:51:01 -0700, Lisa
wrote:

I am sorry but I am still not sure. I know that I need to do a sumproduct but
I am unsure of how to set it up. When I select sumproduct it gives me the
option of three arrays. I am guessing that I need to select the fields that I
want it to check but I am unsure of how I tell it what to look for.

Lisa

"Gary Brown" wrote:

You want to use the SumProduct( ) function.
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Lisa" wrote:

Dear All,

I have designed an excel database in Excel 2007, but then tried to implement
it at work only to find that the countifs function doesnt work in excel 2000.
The two columns I am trying to count have pick lists, not sure if this makes
a difference. The formula in 2007 looks like as follows
=COUNTIFS(DurhamVacs!G25:G1582,"apr",DurhamVacs!N2 5:N1582,"advertised")

What I am trying to achive is, a formula that counts where one field = Apr
and another field = advertised e.g. number of vacancies advertised in April.
I hope this make sense

Lisa

Countifs in Excel 2000? Alternative solution sought!!!
 

Try
=SUMPRODUCT(--(DurhamVacs!G25:G1582="apr"),--(DurhamVacs!N25:N1582="advertised"))

of course that's all on one line in an Excel cell. Put it in your 2007
workbook along with the current SUMIFS() and compare the results for
verification.
Beauty is that it works in 2007, 2003, XP, and 2000.

"Lisa" wrote:

I am sorry but I am still not sure. I know that I need to do a sumproduct but
I am unsure of how to set it up. When I select sumproduct it gives me the
option of three arrays. I am guessing that I need to select the fields that I
want it to check but I am unsure of how I tell it what to look for.

Lisa

"Gary Brown" wrote:

You want to use the SumProduct( ) function.
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Lisa" wrote:

Dear All,

I have designed an excel database in Excel 2007, but then tried to implement
it at work only to find that the countifs function doesnt work in excel 2000.
The two columns I am trying to count have pick lists, not sure if this makes
a difference. The formula in 2007 looks like as follows
=COUNTIFS(DurhamVacs!G25:G1582,"apr",DurhamVacs!N2 5:N1582,"advertised")

What I am trying to achive is, a formula that counts where one field = Apr
and another field = advertised e.g. number of vacancies advertised in April.
I hope this make sense

Lisa