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

hi
Thanks, I had meant to delete the asterisk when copying the OP's code but
forgot.

--
Regards
Roger Govier

"daddylonglegs" wrote in message
...
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

Hi

Now it's working perfect.
Thanks a lot for the help :-)

Amir
--
www.amir2000.nl


"Roger Govier" wrote:

hi
Thanks, I had meant to delete the asterisk when copying the OP's code but
forgot.

--
Regards
Roger Govier

"daddylonglegs" wrote in message
...
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

Hi again

i try it for a while and I found that it's not calculating the way i needed.
I need it to look in 3 columns of dates and if the date is correct, than I
need it to count the word box.
the word box is changing;
box 1
box 2
box 3
etc...

The formula that you guys suggested is calculating the date.

Is anyone can help?
it's very urgent.

Many thanks

Amir
--
www.amir2000.nl


"amir2000" wrote:

Hi

Now it's working perfect.
Thanks a lot for the help :-)

Amir
--
www.amir2000.nl


"Roger Govier" wrote:

hi
Thanks, I had meant to delete the asterisk when copying the OP's code but
forgot.

--
Regards
Roger Govier

"daddylonglegs" wrote in message
...
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

The formulas shown do count the occurences of the work box.

Post the formula you are using (make sure you copy it from the formula
box -- don't just type it in the message). Tell us what result you are
getting, and what you expected.

Regards,
Fred.

"amir2000" wrote in message
...
Hi again

i try it for a while and I found that it's not calculating the way i
needed.
I need it to look in 3 columns of dates and if the date is correct, than I
need it to count the word box.
the word box is changing;
box 1
box 2
box 3
etc...

The formula that you guys suggested is calculating the date.

Is anyone can help?
it's very urgent.

Many thanks

Amir
--
www.amir2000.nl


"amir2000" wrote:

Hi

Now it's working perfect.
Thanks a lot for the help :-)

Amir
--
www.amir2000.nl


"Roger Govier" wrote:

hi
Thanks, I had meant to delete the asterisk when copying the OP's code
but
forgot.

--
Regards
Roger Govier

"daddylonglegs" wrote in message
...
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

Hi
I looked in your forum and i found the correct way to do it.
Till now I searched a way to count the word box but now it counting all the
non blanks.
I will try this one today:

=SUMPRODUCT(--($AL$5:$AL$29=39601)+($AN$5:$AN$29=39601)+($AP$5:$ AP$29=39601), --($AS$5:$AS$29<""))

Thanks,

Amir
--
www.amir2000.nl


"Fred Smith" wrote:

The formulas shown do count the occurences of the work box.

Post the formula you are using (make sure you copy it from the formula
box -- don't just type it in the message). Tell us what result you are
getting, and what you expected.

Regards,
Fred.

"amir2000" wrote in message
...
Hi again

i try it for a while and I found that it's not calculating the way i
needed.
I need it to look in 3 columns of dates and if the date is correct, than I
need it to count the word box.
the word box is changing;
box 1
box 2
box 3
etc...

The formula that you guys suggested is calculating the date.

Is anyone can help?
it's very urgent.

Many thanks

Amir
--
www.amir2000.nl


"amir2000" wrote:

Hi

Now it's working perfect.
Thanks a lot for the help :-)

Amir
--
www.amir2000.nl


"Roger Govier" wrote:

hi
Thanks, I had meant to delete the asterisk when copying the OP's code
but
forgot.

--
Regards
Roger Govier

"daddylonglegs" wrote in message
...
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

Hi All again,

I tried and tried and tried but no success.

Can anyone please have a look and try to help me:

www.amir2000.nl/example.mht


************************
A B C D E F G H
I J K L M N O P Q

1)QTY 1 Date 1 QTY 2 Date 2 QTY 3 Date 3 total Line 1 Line 2 Line 3
Line 4 Line 5 Line 6 Line 7 Line 8 Line 9 Line 10
2)10 02/06/2008 10 box 1
box 1 box 1 box 1 box 1 box 1 box 1 box 1 box 1 box 1
3) 5 03/06/2008 5 04/06/2008 10 box 1 box 1
box 1 box 1 box 1 box 2 box 2 box 2 box 2 box 2
4) 3 02/06/2008 2 03/06/2008 5 04/06/2008 10 box 1 box 1 box 1 box
1 box 1 box 1 box 1 box 1 box 1 box 1
5) 5 12/06/2008 5 05/06/2008 10 box 2 box 2
box 2 box 2 box 2 box 2 box 2 box 2 box 2 box 2
6)10 02/06/2008 10 box 1
box 1 box 1 box 1 box 1 box 1 box 1 box 1 box 1 box 1
7)10 06/06/2008 10 box 4
box 4 box 4 box 4 box 4 box 4 box 4 box 4 box 4 box 4


Total: 60 6 6 6 6 6 6 6 6 6 6 COUNTA(Q2:Q7)
***********************************************

COUNTIFS($B$2:$B$7,39601,$H$2:$H$7,"box*")+COUNTIF S($D$2:$D$7,39601,$H$2:$H$7,"box*")+COUNTIFS($F$2: $F$7,39601,$H$2:$H$7,"box*")

02/06/2008 3
03/06/2008 2
04/06/2008 2
05/06/2008 1
06/06/2008 1
Total 9
**********************************************
* How can I avoid the diference of the 2 pieces?
* What can I use insted of countifs - we using Office 2000.
* I need it to count by the line # according to the date only once.

* The total should match.
* I need to know how many pieces processed per line per day.

Many Thanks,

Amir
--
www.amir2000.nl


"amir2000" wrote:

Hi
I looked in your forum and i found the correct way to do it.
Till now I searched a way to count the word box but now it counting all the
non blanks.
I will try this one today:

=SUMPRODUCT(--($AL$5:$AL$29=39601)+($AN$5:$AN$29=39601)+($AP$5:$ AP$29=39601), --($AS$5:$AS$29<""))

Thanks,

Amir
--
www.amir2000.nl


"Fred Smith" wrote:

The formulas shown do count the occurences of the work box.

Post the formula you are using (make sure you copy it from the formula
box -- don't just type it in the message). Tell us what result you are
getting, and what you expected.

Regards,
Fred.

"amir2000" wrote in message
...
Hi again

i try it for a while and I found that it's not calculating the way i
needed.
I need it to look in 3 columns of dates and if the date is correct, than I
need it to count the word box.
the word box is changing;
box 1
box 2
box 3
etc...

The formula that you guys suggested is calculating the date.

Is anyone can help?
it's very urgent.

Many thanks

Amir
--
www.amir2000.nl


"amir2000" wrote:

Hi

Now it's working perfect.
Thanks a lot for the help :-)

Amir
--
www.amir2000.nl


"Roger Govier" wrote:

hi
Thanks, I had meant to delete the asterisk when copying the OP's code
but
forgot.

--
Regards
Roger Govier

"daddylonglegs" wrote in message
...
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

amir2000 wrote...
....
I tried and tried and tried but no success.
....
=COUNTIFS($B$2:$B$7,39601,$H$2:$H$7,"box*")
+COUNTIFS($D$2:$D$7,39601,$H$2:$H$7,"box*")
+COUNTIFS($F$2:$F$7,39601,$H$2:$H$7,"box*")
....

Others seem to have failed to notice that your Excel 2007 formula,
which you claim is working correctly in Excel 2007, is the SUM of 3
SEPARATE conditional sums.

Try the following.

=SUMPRODUCT(--(LEFT($H$2:$H$7,3)="box"),($B$2:$B$7=39601)+($D$2: $D
$7=39601)+($F$2:$F$7=39601))

Hi
Thanks, but. its counting ok but the target is that it will count only once
under the "line 1" column even if in the next date column i got the second
shipment.

The thing is that it's possible to get 3 shippments in diferent dates and I
need it to count only one of them.

Thanks,

Amir

--
www.amir2000.nl


"Harlan Grove" wrote:

amir2000 wrote...
....
I tried and tried and tried but no success.
....
=COUNTIFS($B$2:$B$7,39601,$H$2:$H$7,"box*")
+COUNTIFS($D$2:$D$7,39601,$H$2:$H$7,"box*")
+COUNTIFS($F$2:$F$7,39601,$H$2:$H$7,"box*")
....

Others seem to have failed to notice that your Excel 2007 formula,
which you claim is working correctly in Excel 2007, is the SUM of 3
SEPARATE conditional sums.

Try the following.

=SUMPRODUCT(--(LEFT($H$2:$H$7,3)="box"),($B$2:$B$7=39601)+($D$2: $D
$7=39601)+($F$2:$F$7=39601))

amir2000 wrote...
Thanks, but. its counting ok but the target is that it will count only once
under the "line 1" column even if in the next date column i got the second
shipment.

The thing is that it's possible to get 3 shippments in diferent dates and I
need it to count only one of them.

Then your original Excel 2007 formula using the COUNTIFS function
would also be producing incorrect results. I'll assume that's the
case.

Try

=SUMPRODUCT(--(LEFT($H$2:$H$7,3)="box"),
--(($B$2:$B$7=39601)+($D$2:$D$7=39601)+($F$2:$F$7=39 601)0))

Yes you are right also the countifs is wrong.
And the one that you suggested is also wrong...
I'm keep trying and cant get this result:
I need it to show me that in this example:
On 02/06/08 - 2
On 03/06/08 - 1
On 06/06/08 - 1

You think it's possible to tell it to count only once and not every date to
count it again?

Thanks,

Amir
--
www.amir2000.nl


"Harlan Grove" wrote:

amir2000 wrote...
Thanks, but. its counting ok but the target is that it will count only once
under the "line 1" column even if in the next date column i got the second
shipment.

The thing is that it's possible to get 3 shippments in diferent dates and I
need it to count only one of them.

Then your original Excel 2007 formula using the COUNTIFS function
would also be producing incorrect results. I'll assume that's the
case.

Try

=SUMPRODUCT(--(LEFT($H$2:$H$7,3)="box"),
--(($B$2:$B$7=39601)+($D$2:$D$7=39601)+($F$2:$F$7=39 601)0))

amir2000 wrote....
....
You think it's possible to tell it to count only once and not every date to
count it again?
....

Example. In B2:H7 (columns C, E and G blank, dates in yyyy-mm-dd
format),

2008-06-03 2008-06-02 2008-06-01 box 2
2008-06-02 2008-06-01 2008-06-03 box 3
2008-06-02 2008-06-01 2008-06-03 --
2008-06-02 2008-06-02 2008-06-01 box 5
2008-06-02 2008-06-02 2008-06-01 --
2008-06-03 2008-06-03 2008-06-03 box 7

The formula

=SUMPRODUCT(--(LEFT($H$2:$H$7,3)="box"),
--(($B$2:$B$7=39601)+($D$2:$D$7=39601)+($F$2:$F$7=39 601)0))

returns 3.

Isn't that the correct result for this data? Note that B5 and D5 both
equal the date 2008-06-02, which equals 39601, but the 'box' in column
H is only counted once.

Thanks, now it's ok for the same date.
But, I need it not to read again if the next date is in the next date column.
It need to count each cell of box once regarding the date but showing me in
which date it was done.
I see it as a chalenge ;-)

Amir
--
www.amir2000.nl


"Harlan Grove" wrote:

amir2000 wrote....
....
You think it's possible to tell it to count only once and not every date to
count it again?
....

Example. In B2:H7 (columns C, E and G blank, dates in yyyy-mm-dd
format),

2008-06-03 2008-06-02 2008-06-01 box 2
2008-06-02 2008-06-01 2008-06-03 box 3
2008-06-02 2008-06-01 2008-06-03 --
2008-06-02 2008-06-02 2008-06-01 box 5
2008-06-02 2008-06-02 2008-06-01 --
2008-06-03 2008-06-03 2008-06-03 box 7

The formula

=SUMPRODUCT(--(LEFT($H$2:$H$7,3)="box"),
--(($B$2:$B$7=39601)+($D$2:$D$7=39601)+($F$2:$F$7=39 601)0))

returns 3.

Isn't that the correct result for this data? Note that B5 and D5 both
equal the date 2008-06-02, which equals 39601, but the 'box' in column
H is only counted once.

amir2000 wrote...
....
It need to count each cell of box once regarding the date but
showing me in which date it was done.
....

Unclear.

Using your example from a few responses back, with *ALL* dates
formatted as yyyymmdd,

A1:H7
QTY 1__Date 1____QTY 2__Date 2____QTY 3__Date 3____total__Line 1
___10__20080206___________________________________ ____10__box 1
____5__20080306______5__19000105__________________ ____10__box 1
____3__20080206______2__19000102______5__20080406_ ____10__box 1
____5__20081206______5__19000105__________________ ____10__box 2
___10__20080206___________________________________ ____10__box 1
___10__20080606___________________________________ ____10__box 4


And in another range, which I'll assume would be B11:C17,

20080206__3
20080306__2
20080406__2
20080506__1
20080606__1

Total_____9


You want the formulas for C11:C17. Try

=SUMPRODUCT(--(LEFT($H$2:$H$7,3)="box"),
--(($B$2:$B$7=B11)+($D$2:$D$7=B11)+($F$2:$F$7=B11)0 ))

in C11 and fill C11 down into C12:C15. These formulas return the
following results in C11:C15.

3
2
2
1
1