countifs - what is instead in excel 2000???

[amir2000]

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

 

[Roger Govier]

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

 

[Max]

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

 

[amir2000]

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

 

[Fred Smith]

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

Regards,
Fred.

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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)

 

[daddylonglegs]

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

 

[daddylonglegs]

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

 

[Roger Govier]

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

 

[amir2000]

Hi

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

Amir

 

[amir2000]

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

 

[Fred Smith]

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]

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

 

[amir2000]

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*")+COUNTIFS($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

 

[Harlan Grove]

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]

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

 

[Harlan Grove]

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=39601)>0))

 

[amir2000]

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