countifs - what is instead in excel 2000???

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))