countifs - what is instead in excel 2000???

Hi
I used in office2007 the function countifs, in office2000 it's not working.
What can i use instead?

Example:


=countifs(AL5:AL29,=39601,AS5:AS29,box*)+countifs( AN5:AN29,=39601,AS5:AS29,box*)+countifs(AP5:AP29,= 39601,AS5:AS29,box*)


- I need to count the date from 3 columns and if its true then to count the
word box(number coming after).

Anyone can help??

Thanks,

Amir

--
www.amir2000.nl

Hi Amir
Try
=SUMPRODUCT((AL5:AL29=39601)*(AN5:AN29=39601)*(AP5 :AP29=39601)*(ISNUMBER(FIND("box",AS5:AS29))))

--
Regards
Roger Govier

"amir2000" wrote in message
...
Hi
I used in office2007 the function countifs, in office2000 it's not
working.
What can i use instead?

Example:


=countifs(AL5:AL29,=39601,AS5:AS29,box*)+countifs( AN5:AN29,=39601,AS5:AS29,box*)+countifs(AP5:AP29,= 39601,AS5:AS29,box*)


- I need to count the date from 3 columns and if its true then to count
the
word box(number coming after).

Anyone can help??

Thanks,

Amir

--
www.amir2000.nl

Think one option is Sumproduct, in this manner

Eg in D1: =sumproduct((A1:A10=111)*(B1:B10="xxx"))
will return the count of rows ("countifs") where
A1:A10 contains the number: 111,
and
B1:B10 contains the text: "xxx"

Adapt /add-on to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"amir2000" wrote:
I used in office2007 the function countifs, in office2000 it's not working.
What can i use instead?

Example:


=countifs(AL5:AL29,=39601,AS5:AS29,box*)+countifs( AN5:AN29,=39601,AS5:AS29,box*)+countifs(AP5:AP29,= 39601,AS5:AS29,box*)


- I need to count the date from 3 columns and if its true then to count the
word box(number coming after).

Anyone can help??

Thanks,

Amir

--
www.amir2000.nl

Hi Again,
Thanks but it does not work.

a b c
1 19/05/08 20/05/08 box 1
2 19/05/08 20/05/08 box 1
3 19/05/08 20/05/08 box 2
4 20/05/08 21/05/08 box 2
5 20/05/08 21/05/08 box 3

I tried the solutions that you gave me but it stays empty.

Thanks,
Amir
---
www.amir2000.nl


"Max" wrote:

Think one option is Sumproduct, in this manner

Eg in D1: =sumproduct((A1:A10=111)*(B1:B10="xxx"))
will return the count of rows ("countifs") where
A1:A10 contains the number: 111,
and
B1:B10 contains the text: "xxx"

Adapt /add-on to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"amir2000" wrote:
I used in office2007 the function countifs, in office2000 it's not working.
What can i use instead?

Example:


=countifs(AL5:AL29,=39601,AS5:AS29,box*)+countifs( AN5:AN29,=39601,AS5:AS29,box*)+countifs(AP5:AP29,= 39601,AS5:AS29,box*)


- I need to count the date from 3 columns and if its true then to count the
word box(number coming after).

Anyone can help??

Thanks,

Amir

--
www.amir2000.nl

Let's meet half way, Amir. Why don't you tell us what formula you used. Then
we can provide solutions.

Regards,
Fred.

"amir2000" wrote in message
...
Hi Again,
Thanks but it does not work.

a b c
1 19/05/08 20/05/08 box 1
2 19/05/08 20/05/08 box 1
3 19/05/08 20/05/08 box 2
4 20/05/08 21/05/08 box 2
5 20/05/08 21/05/08 box 3

I tried the solutions that you gave me but it stays empty.

Thanks,
Amir
---
www.amir2000.nl


"Max" wrote:

Think one option is Sumproduct, in this manner

Eg in D1: =sumproduct((A1:A10=111)*(B1:B10="xxx"))
will return the count of rows ("countifs") where
A1:A10 contains the number: 111,
and
B1:B10 contains the text: "xxx"

Adapt /add-on to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"amir2000" wrote:
I used in office2007 the function countifs, in office2000 it's not
working.
What can i use instead?

Example:


=countifs(AL5:AL29,=39601,AS5:AS29,box*)+countifs( AN5:AN29,=39601,AS5:AS29,box*)+countifs(AP5:AP29,= 39601,AS5:AS29,box*)


- I need to count the date from 3 columns and if its true then to count
the
word box(number coming after).

Anyone can help??

Thanks,

Amir

--
www.amir2000.nl

Roger's should work for you if the columns are correct are there are those
dates.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"amir2000" wrote in message
...
Hi Again,
Thanks but it does not work.

a b c
1 19/05/08 20/05/08 box 1
2 19/05/08 20/05/08 box 1
3 19/05/08 20/05/08 box 2
4 20/05/08 21/05/08 box 2
5 20/05/08 21/05/08 box 3

I tried the solutions that you gave me but it stays empty.

Thanks,
Amir
---
www.amir2000.nl


"Max" wrote:

Think one option is Sumproduct, in this manner

Eg in D1: =sumproduct((A1:A10=111)*(B1:B10="xxx"))
will return the count of rows ("countifs") where
A1:A10 contains the number: 111,
and
B1:B10 contains the text: "xxx"

Adapt /add-on to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"amir2000" wrote:
I used in office2007 the function countifs, in office2000 it's not
working.
What can i use instead?

Example:


=countifs(AL5:AL29,=39601,AS5:AS29,box*)+countifs( AN5:AN29,=39601,AS5:AS29,box*)+countifs(AP5:AP29,= 39601,AS5:AS29,box*)


- I need to count the date from 3 columns and if its true then to count
the
word box(number coming after).

Anyone can help??

Thanks,

Amir

--
www.amir2000.nl

Hi Again

Sorry, but it does not work:

=SUMPRODUCT((AL5:AL29=39601)*(AN5:AN29=39601)*AP5: AP29=39601)*(ISNUMBER(FIND("box*",AS5:AS29))

that's how i wrote it and the result is:
-

it's like it cant find the data.

Could you explain me again please?

Thanks,

Amir

--
www.amir2000.nl


"Bob Phillips" wrote:

Roger's should work for you if the columns are correct are there are those
dates.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"amir2000" wrote in message
...
Hi Again,
Thanks but it does not work.

a b c
1 19/05/08 20/05/08 box 1
2 19/05/08 20/05/08 box 1
3 19/05/08 20/05/08 box 2
4 20/05/08 21/05/08 box 2
5 20/05/08 21/05/08 box 3

I tried the solutions that you gave me but it stays empty.

Thanks,
Amir
---
www.amir2000.nl


"Max" wrote:

Think one option is Sumproduct, in this manner

Eg in D1: =sumproduct((A1:A10=111)*(B1:B10="xxx"))
will return the count of rows ("countifs") where
A1:A10 contains the number: 111,
and
B1:B10 contains the text: "xxx"

Adapt /add-on to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"amir2000" wrote:
I used in office2007 the function countifs, in office2000 it's not
working.
What can i use instead?

Example:


=countifs(AL5:AL29,=39601,AS5:AS29,box*)+countifs( AN5:AN29,=39601,AS5:AS29,box*)+countifs(AP5:AP29,= 39601,AS5:AS29,box*)


- I need to count the date from 3 columns and if its true then to count
the
word box(number coming after).

Anyone can help??

Thanks,

Amir

--
www.amir2000.nl

It should be

=SUMPRODUCT((AL5:AL29=39601)*(AN5:AN29=39601)*(AP5 :AP29=39601)*(ISNUMBER(FIND("box*",AS5:AS29))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"amir2000" wrote in message
...
Hi Again

Sorry, but it does not work:

=SUMPRODUCT((AL5:AL29=39601)*(AN5:AN29=39601)*AP5: AP29=39601)*(ISNUMBER(FIND("box*",AS5:AS29))

that's how i wrote it and the result is:
-

it's like it cant find the data.

Could you explain me again please?

Thanks,

Amir

--
www.amir2000.nl


"Bob Phillips" wrote:

Roger's should work for you if the columns are correct are there are
those
dates.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"amir2000" wrote in message
...
Hi Again,
Thanks but it does not work.

a b c
1 19/05/08 20/05/08 box 1
2 19/05/08 20/05/08 box 1
3 19/05/08 20/05/08 box 2
4 20/05/08 21/05/08 box 2
5 20/05/08 21/05/08 box 3

I tried the solutions that you gave me but it stays empty.

Thanks,
Amir
---
www.amir2000.nl


"Max" wrote:

Think one option is Sumproduct, in this manner

Eg in D1: =sumproduct((A1:A10=111)*(B1:B10="xxx"))
will return the count of rows ("countifs") where
A1:A10 contains the number: 111,
and
B1:B10 contains the text: "xxx"

Adapt /add-on to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"amir2000" wrote:
I used in office2007 the function countifs, in office2000 it's not
working.
What can i use instead?

Example:


=countifs(AL5:AL29,=39601,AS5:AS29,box*)+countifs( AN5:AN29,=39601,AS5:AS29,box*)+countifs(AP5:AP29,= 39601,AS5:AS29,box*)


- I need to count the date from 3 columns and if its true then to
count
the
word box(number coming after).

Anyone can help??

Thanks,

Amir

--
www.amir2000.nl

FIND function doesn't accept wildcards, if you try to find "box*" then this
will look for "box" followed by a literal asterisk. Use the syntax that
Roger suggested, i.e. without the asterisk, that will work for "box"
contained amongst other text.

As you say that "box" would be the first 3 characters you could search for
that specifically with

=SUMPRODUCT((AL5:AL29=39601)*(AN5:AN29=39601)*(AP5 :AP29=39601)*(LEFT(AS5:AS29,3)="box"))

"amir2000" wrote:

Hi Again

Sorry, but it does not work:

=SUMPRODUCT((AL5:AL29=39601)*(AN5:AN29=39601)*AP5: AP29=39601)*(ISNUMBER(FIND("box*",AS5:AS29))

that's how i wrote it and the result is:
-

it's like it cant find the data.

Could you explain me again please?

Thanks,

Amir

--
www.amir2000.nl


"Bob Phillips" wrote:

Roger's should work for you if the columns are correct are there are those
dates.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"amir2000" wrote in message
...
Hi Again,
Thanks but it does not work.

a b c
1 19/05/08 20/05/08 box 1
2 19/05/08 20/05/08 box 1
3 19/05/08 20/05/08 box 2
4 20/05/08 21/05/08 box 2
5 20/05/08 21/05/08 box 3

I tried the solutions that you gave me but it stays empty.

Thanks,
Amir
---
www.amir2000.nl


"Max" wrote:

Think one option is Sumproduct, in this manner

Eg in D1: =sumproduct((A1:A10=111)*(B1:B10="xxx"))
will return the count of rows ("countifs") where
A1:A10 contains the number: 111,
and
B1:B10 contains the text: "xxx"

Adapt /add-on to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"amir2000" wrote:
I used in office2007 the function countifs, in office2000 it's not
working.
What can i use instead?

Example:


=countifs(AL5:AL29,=39601,AS5:AS29,box*)+countifs( AN5:AN29,=39601,AS5:AS29,box*)+countifs(AP5:AP29,= 39601,AS5:AS29,box*)


- I need to count the date from 3 columns and if its true then to count
the
word box(number coming after).

Anyone can help??

Thanks,

Amir

--
www.amir2000.nl

Note: the formula I suggested will only count rows where all columns, AL, AN
and AP contain the date AND AS begins with "box". Your multiple COUNTIFS
formula counts the date columns separately so to replicate that exactly you'd
need a small change, i.e.

=SUMPRODUCT((AL5:AL29=39601)+(AN5:AN29=39601)+(AP5 :AP29=39601),--(LEFT(AS5:AS29,3)="box"))

"daddylonglegs" wrote:

FIND function doesn't accept wildcards, if you try to find "box*" then this
will look for "box" followed by a literal asterisk. Use the syntax that
Roger suggested, i.e. without the asterisk, that will work for "box"
contained amongst other text.

As you say that "box" would be the first 3 characters you could search for
that specifically with

=SUMPRODUCT((AL5:AL29=39601)*(AN5:AN29=39601)*(AP5 :AP29=39601)*(LEFT(AS5:AS29,3)="box"))

"amir2000" wrote:

Hi Again

Sorry, but it does not work:

=SUMPRODUCT((AL5:AL29=39601)*(AN5:AN29=39601)*AP5: AP29=39601)*(ISNUMBER(FIND("box*",AS5:AS29))

that's how i wrote it and the result is:
-

it's like it cant find the data.

Could you explain me again please?

Thanks,

Amir

--
www.amir2000.nl


"Bob Phillips" wrote:

Roger's should work for you if the columns are correct are there are those
dates.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"amir2000" wrote in message
...
Hi Again,
Thanks but it does not work.

a b c
1 19/05/08 20/05/08 box 1
2 19/05/08 20/05/08 box 1
3 19/05/08 20/05/08 box 2
4 20/05/08 21/05/08 box 2
5 20/05/08 21/05/08 box 3

I tried the solutions that you gave me but it stays empty.

Thanks,
Amir
---
www.amir2000.nl


"Max" wrote:

Think one option is Sumproduct, in this manner

Eg in D1: =sumproduct((A1:A10=111)*(B1:B10="xxx"))
will return the count of rows ("countifs") where
A1:A10 contains the number: 111,
and
B1:B10 contains the text: "xxx"

Adapt /add-on to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"amir2000" wrote:
I used in office2007 the function countifs, in office2000 it's not
working.
What can i use instead?

Example:


=countifs(AL5:AL29,=39601,AS5:AS29,box*)+countifs( AN5:AN29,=39601,AS5:AS29,box*)+countifs(AP5:AP29,= 39601,AS5:AS29,box*)


- I need to count the date from 3 columns and if its true then to count
the
word box(number coming after).

Anyone can help??

Thanks,

Amir

--
www.amir2000.nl