hi

i got a problem with function Countif and sumproblem

My problem is that i need to match 3 diff critieria , if all of them matches
, on a cell , it will count 1 .

1st spread sheet

Date Status Place
010906 Accepted L.A
010906 Accepted L.A
010906 Pending L.A
020906 Accepeted chiago

If the date is "010906" , then the status is either accepted or pending and
the place is L.A . i wan the cell below to count , how to i do that ? do i
need to create a dummy column ?
2nd spread sheet

place 010906
L.A 3

=SUMPRODUCT(--(Sheet1!$A$1:$A$100=B$1),--(Sheet1!$C$1:$C$100=$A2))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"numbernine" wrote in message
...
hi

i got a problem with function Countif and sumproblem

My problem is that i need to match 3 diff critieria , if all of them
matches
, on a cell , it will count 1 .

1st spread sheet

Date Status Place
010906 Accepted L.A
010906 Accepted L.A
010906 Pending L.A
020906 Accepeted chiago

If the date is "010906" , then the status is either accepted or pending
and
the place is L.A . i wan the cell below to count , how to i do that ? do i
need to create a dummy column ?
2nd spread sheet

place 010906
L.A 3

Hi,

Thank for your answer ,i think i explained wrongly in my question . actually
i wanted to match the 3 column word by word , which mean on the 1st column
the word is "0109/06" , 2nd cell is "Accepted or Pending" , then the 3rd cell
is "L.A " . If all these appear on the same row , on the 2nd spread sheet on
a partcular same will count as '1' .

I tried using this formula but i could not count the date(1st column) and
also the word "accepted " or ""pending(2rd column).

=SUMPRODUCT((Sheet1!A2:A22="01/09/06")*(Sheet1!B2:B22="Accepted"
or"Pending")*(Sheet1!C2:C22="L.A"))

Thank alot !!!
Cheers,


"Bob Phillips" wrote:

=SUMPRODUCT(--(Sheet1!$A$1:$A$100=B$1),--(Sheet1!$C$1:$C$100=$A2))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"numbernine" wrote in message
...
hi

i got a problem with function Countif and sumproblem

My problem is that i need to match 3 diff critieria , if all of them
matches
, on a cell , it will count 1 .

1st spread sheet

Date Status Place
010906 Accepted L.A
010906 Accepted L.A
010906 Pending L.A
020906 Accepeted chiago

If the date is "010906" , then the status is either accepted or pending
and
the place is L.A . i wan the cell below to count , how to i do that ? do i
need to create a dummy column ?
2nd spread sheet

place 010906
L.A 3

Hi

Try
=SUMPRODUCT((Sheet1!A2:A22=--"01/09/2006")*
((Sheet1!B2:B22="Accepted")+ (Sheet1!B2:B22="Pending"))*
(Sheet1!C2:C22="L.A"))

I had to use the double unary minus "--" in front of the date to get it
to recognise it as a date.
Personally, I prefer to use =DATE(2006,9,1) rather than --"01/09/2006"

--
Regards

Roger Govier


"numbernine" wrote in message
...
Hi,

Thank for your answer ,i think i explained wrongly in my question .
actually
i wanted to match the 3 column word by word , which mean on the 1st
column
the word is "0109/06" , 2nd cell is "Accepted or Pending" , then the
3rd cell
is "L.A " . If all these appear on the same row , on the 2nd spread
sheet on
a partcular same will count as '1' .

I tried using this formula but i could not count the date(1st column)
and
also the word "accepted " or ""pending(2rd column).

=SUMPRODUCT((Sheet1!A2:A22="01/09/06")*(Sheet1!B2:B22="Accepted"
or"Pending")*(Sheet1!C2:C22="L.A"))

Thank alot !!!
Cheers,


"Bob Phillips" wrote:

=SUMPRODUCT(--(Sheet1!$A$1:$A$100=B$1),--(Sheet1!$C$1:$C$100=$A2))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"numbernine" wrote in message
...
hi

i got a problem with function Countif and sumproblem

My problem is that i need to match 3 diff critieria , if all of
them
matches
, on a cell , it will count 1 .

1st spread sheet

Date Status Place
010906 Accepted L.A
010906 Accepted L.A
010906 Pending L.A
020906 Accepeted chiago

If the date is "010906" , then the status is either accepted or
pending
and
the place is L.A . i wan the cell below to count , how to i do that
? do i
need to create a dummy column ?
2nd spread sheet

place 010906
L.A 3

I would put the values to test against in cells and check against those as
in my original, other than that

=SUMPRODUCT((Sheet1!A2:A22=--"2006-09-01")*
(Sheet1!B2:B22={"Accepted","Pending"})*
(Sheet1!C2:C22="LA"))


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"numbernine" wrote in message
...
Hi,

Thank for your answer ,i think i explained wrongly in my question .
actually
i wanted to match the 3 column word by word , which mean on the 1st column
the word is "0109/06" , 2nd cell is "Accepted or Pending" , then the 3rd
cell
is "L.A " . If all these appear on the same row , on the 2nd spread sheet
on
a partcular same will count as '1' .

I tried using this formula but i could not count the date(1st column) and
also the word "accepted " or ""pending(2rd column).

=SUMPRODUCT((Sheet1!A2:A22="01/09/06")*(Sheet1!B2:B22="Accepted"
or"Pending")*(Sheet1!C2:C22="L.A"))

Thank alot !!!
Cheers,


"Bob Phillips" wrote:

=SUMPRODUCT(--(Sheet1!$A$1:$A$100=B$1),--(Sheet1!$C$1:$C$100=$A2))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"numbernine" wrote in message
...
hi

i got a problem with function Countif and sumproblem

My problem is that i need to match 3 diff critieria , if all of them
matches
, on a cell , it will count 1 .

1st spread sheet

Date Status Place
010906 Accepted L.A
010906 Accepted L.A
010906 Pending L.A
020906 Accepeted chiago

If the date is "010906" , then the status is either accepted or
pending
and
the place is L.A . i wan the cell below to count , how to i do that ?
do i
need to create a dummy column ?
2nd spread sheet

place 010906
L.A 3