More than one variable for Countif
 

Hi -

Really stumped on this one. I have tried many different formula's using
SUMPRODUCT, And, IF in conjunction with Countif but still can't seem to get
it to work.

I have a worksheet with dates listed down column B called "Schedule". I need
to look in another worksheet, find two separate colunms true; if they are
both true then count. I continue to get the #NUM! error no matter which way I
approach this. Any help would surely be appreciated. Example of the
"SUMPRODUCT" approach below [B163 is equal to a date of 06/09/2009 and K2 is
equal to a team number of L01 on the worksheet "Schedule"]


=SUMPRODUCT('Team Worksheet'!J:J=Schedule!B163,'Team Worksheet'!N:N=Schedule!
K2)

Thanks for any help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200905/1

More than one variable for Countif
 

You can't use whole columns like J:J, try using a range like J1;J5000
Regards,
Alan.
"Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message
news:9596092e4be14@uwe...
Hi -

Really stumped on this one. I have tried many different formula's using
SUMPRODUCT, And, IF in conjunction with Countif but still can't seem to
get
it to work.

I have a worksheet with dates listed down column B called "Schedule". I
need
to look in another worksheet, find two separate colunms true; if they are
both true then count. I continue to get the #NUM! error no matter which
way I
approach this. Any help would surely be appreciated. Example of the
"SUMPRODUCT" approach below [B163 is equal to a date of 06/09/2009 and K2
is
equal to a team number of L01 on the worksheet "Schedule"]


=SUMPRODUCT('Team Worksheet'!J:J=Schedule!B163,'Team
Worksheet'!N:N=Schedule!
K2)

Thanks for any help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200905/1

More than one variable for Countif
 

Hi

=SUMPRODUCT(--('Team Worksheet'!J:J=Schedule!B163),--('Team
Worksheet'!N:N=Schedule!K2))

The above formula will only work in XL2007. In previous versions, you can
not use a range like J:J, but something like J1:J1000.

Hopes this helps.

Per

"Carrie_Loos via OfficeKB.com" <u34134@uwe skrev i meddelelsen
news:9596092e4be14@uwe...
Hi -

Really stumped on this one. I have tried many different formula's using
SUMPRODUCT, And, IF in conjunction with Countif but still can't seem to
get
it to work.

I have a worksheet with dates listed down column B called "Schedule". I
need
to look in another worksheet, find two separate colunms true; if they are
both true then count. I continue to get the #NUM! error no matter which
way I
approach this. Any help would surely be appreciated. Example of the
"SUMPRODUCT" approach below [B163 is equal to a date of 06/09/2009 and K2
is
equal to a team number of L01 on the worksheet "Schedule"]


=SUMPRODUCT('Team Worksheet'!J:J=Schedule!B163,'Team
Worksheet'!N:N=Schedule!
K2)

Thanks for any help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200905/1

More than one variable for Countif
 

Hi,

and don't forget that the ranges should have same length (fi J1:J5000
and N1:N5000)

HTH
Alan;332752 Wrote:
You can't use whole columns like J:J, try using a range like J1;J5000
Regards,
Alan.
"Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message
news:9596092e4be14@uwe...
Hi -

Really stumped on this one. I have tried many different formula's
using
SUMPRODUCT, And, IF in conjunction with Countif but still can't seem
to
get
it to work.

I have a worksheet with dates listed down column B called "Schedule".
I
need
to look in another worksheet, find two separate colunms true; if they
are
both true then count. I continue to get the #NUM! error no matter
which
way I
approach this. Any help would surely be appreciated. Example of the
"SUMPRODUCT" approach below [B163 is equal to a date of 06/09/2009
and K2
is
equal to a team number of L01 on the worksheet "Schedule"]


=SUMPRODUCT('Team Worksheet'!J:J=Schedule!B163,'Team
Worksheet'!N:N=Schedule!
K2)

Thanks for any help

--
Message posted via OfficeKB.com
'Excel - using Excel worksheet functions'
(http://www.officekb.com/Uwe/Forums.a...tions/200905/1)
HOW TO GET FURTHER HELP WITH A WORKBOOK
For further help with it why not join our forums (shown in
the link below) it's completely free, if you do join you will have the
opportunity to add attachmnets to your posts so you can add workbooks to
better illustrate your problems and get help directly with them. Also if
you do join please post in this thread (link found below) so that people
who have been following or helping with this query can continue to do
so. :)


--
Pecoflyer

Cheers -
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=92988

More than one variable for Countif
 

Thanks - It did finally remove the #NUM! error but now I am only getting 0's
where I know there should be a count. I need to make sure the values match
and then see..........?

Alan wrote:
You can't use whole columns like J:J, try using a range like J1;J5000
Regards,
Alan.
Hi -

[quoted text clipped - 18 lines]

Thanks for any help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200905/1

More than one variable for Countif
 

Carrie_Loos via OfficeKB.com;332825 Wrote:
Thanks - It did finally remove the #NUM! error but now I am only getting
0's
where I know there should be a count. I need to make sure the values
match
and then see..........?

Alan wrote:
You can't use whole columns like J:J, try using a range like J1;J5000
Regards,
Alan.
Hi -

d text clipped - 18 lines Wrote:


Thanks for any help

--
Message posted via OfficeKB.com
'Excel - using Excel worksheet functions'
(http://www.officekb.com/Uwe/Forums.aspx/excel-functions/200905/1)

Hi,
you can post a sample of your data on our forum if you fel like it


--
Pecoflyer

Cheers -
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=92988

More than one variable for Countif
 

You can reference whole columns but only in Excel 2007. Otherwise change the
references to equally fixed ranges. ie J1:J900 & N1:N900
Try this:
=SUMPRODUCT(--('Team Worksheet'!J:J=Schedule!B163),--('Team
Worksheet'!N:N=Schedule!
K2))

"Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message
news:9596092e4be14@uwe...
Hi -

Really stumped on this one. I have tried many different formula's using
SUMPRODUCT, And, IF in conjunction with Countif but still can't seem to
get
it to work.

I have a worksheet with dates listed down column B called "Schedule". I
need
to look in another worksheet, find two separate colunms true; if they are
both true then count. I continue to get the #NUM! error no matter which
way I
approach this. Any help would surely be appreciated. Example of the
"SUMPRODUCT" approach below [B163 is equal to a date of 06/09/2009 and K2
is
equal to a team number of L01 on the worksheet "Schedule"]


=SUMPRODUCT('Team Worksheet'!J:J=Schedule!B163,'Team
Worksheet'!N:N=Schedule!
K2)

Thanks for any help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200905/1

More than one variable for Countif
 

Well - Still can't get it to work, double checked to make sure variable
matched formats ect... The new formula is =SUMPRODUCT('Team Worksheet'!$J$1:
$J$5000=Schedule!$B163,'Team Worksheet'!$N$1:$N$5000=Schedule!K$2) but brings
back a value of zero. I check and there is at least one that should be
counted.

Set it up on another worksheet for test, still got zeros????

Per Jessen wrote:
Hi

=SUMPRODUCT(--('Team Worksheet'!J:J=Schedule!B163),--('Team
Worksheet'!N:N=Schedule!K2))

The above formula will only work in XL2007. In previous versions, you can
not use a range like J:J, but something like J1:J1000.

Hopes this helps.

Per

Hi -

[quoted text clipped - 18 lines]

Thanks for any help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200905/1

More than one variable for Countif
 

Use the formula supplied by Bassman, it will work,

=SUMPRODUCT(--('Team
Worksheet'!J1:J1000=Schedule!B163),--('TeamWorksheet'!N1:N1000=Schedule!K2))


"Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message
news:959714f1c0c20@uwe...
Well - Still can't get it to work, double checked to make sure variable
matched formats ect... The new formula is =SUMPRODUCT('Team
Worksheet'!$J$1:
$J$5000=Schedule!$B163,'Team Worksheet'!$N$1:$N$5000=Schedule!K$2) but
brings
back a value of zero. I check and there is at least one that should be
counted.

Set it up on another worksheet for test, still got zeros????

Per Jessen wrote:
Hi

=SUMPRODUCT(--('Team Worksheet'!J:J=Schedule!B163),--('Team
Worksheet'!N:N=Schedule!K2))

The above formula will only work in XL2007. In previous versions, you can
not use a range like J:J, but something like J1:J1000.

Hopes this helps.

Per

Hi -

[quoted text clipped - 18 lines]

Thanks for any help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200905/1

More than one variable for Countif
 

Take another look at the formula that Per provided. The double unary
operator "--" coerces the logical "TRUE & FALSE" results of your arrarys
into numeric 1 & 0 which is required for the operation.


"Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message
news:959714f1c0c20@uwe...
Well - Still can't get it to work, double checked to make sure variable
matched formats ect... The new formula is =SUMPRODUCT('Team
Worksheet'!$J$1:
$J$5000=Schedule!$B163,'Team Worksheet'!$N$1:$N$5000=Schedule!K$2) but
brings
back a value of zero. I check and there is at least one that should be
counted.

Set it up on another worksheet for test, still got zeros????

Per Jessen wrote:
Hi

=SUMPRODUCT(--('Team Worksheet'!J:J=Schedule!B163),--('Team
Worksheet'!N:N=Schedule!K2))

The above formula will only work in XL2007. In previous versions, you can
not use a range like J:J, but something like J1:J1000.

Hopes this helps.

Per

Hi -

[quoted text clipped - 18 lines]

Thanks for any help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200905/1

More than one variable for Countif
 

Oh, thanks for the insight, I did not know that - I will give it a try

Bassman62 wrote:
Take another look at the formula that Per provided. The double unary
operator "--" coerces the logical "TRUE & FALSE" results of your arrarys
into numeric 1 & 0 which is required for the operation.

Well - Still can't get it to work, double checked to make sure variable
matched formats ect... The new formula is =SUMPRODUCT('Team
[quoted text clipped - 23 lines]

Thanks for any help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200905/1

More than one variable for Countif
 

Worked beautifully, Thanks

Bassman62 wrote:
Take another look at the formula that Per provided. The double unary
operator "--" coerces the logical "TRUE & FALSE" results of your arrarys
into numeric 1 & 0 which is required for the operation.

Well - Still can't get it to work, double checked to make sure variable
matched formats ect... The new formula is =SUMPRODUCT('Team
[quoted text clipped - 23 lines]

Thanks for any help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200905/1

More than one variable for Countif
 

Worked beautifully, Thanks

Bassman62 wrote:
Take another look at the formula that Per provided. The double unary
operator "--" coerces the logical "TRUE & FALSE" results of your arrarys
into numeric 1 & 0 which is required for the operation.

Well - Still can't get it to work, double checked to make sure variable
matched formats ect... The new formula is =SUMPRODUCT('Team
[quoted text clipped - 23 lines]

Thanks for any help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200905/1

More than one variable for Countif
 

Worked beautifully, Thanks

Bassman62 wrote:
Take another look at the formula that Per provided. The double unary
operator "--" coerces the logical "TRUE & FALSE" results of your arrarys
into numeric 1 & 0 which is required for the operation.

Well - Still can't get it to work, double checked to make sure variable
matched formats ect... The new formula is =SUMPRODUCT('Team
[quoted text clipped - 23 lines]

Thanks for any help

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200905/1

More than one variable for Countif
 

Have a look at this site
http://www.xldynamic.com/source/xld....T.html#classic for a
detailed explanation of the sumproduct syntax
HTH
Bassman62;333015 Wrote:
Take another look at the formula that Per provided. The double unary
operator "--" coerces the logical "TRUE & FALSE" results of your
arrarys
into numeric 1 & 0 which is required for the operation.


"Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message
news:959714f1c0c20@uwe...
Well - Still can't get it to work, double checked to make sure
variable
matched formats ect... The new formula is =SUMPRODUCT('Team
Worksheet'!$J$1:
$J$5000=Schedule!$B163,'Team Worksheet'!$N$1:$N$5000=Schedule!K$2)
but
brings
back a value of zero. I check and there is at least one that should
be
counted.

Set it up on another worksheet for test, still got zeros????

Per Jessen wrote:
Hi

=SUMPRODUCT(--('Team Worksheet'!J:J=Schedule!B163),--('Team
Worksheet'!N:N=Schedule!K2))

The above formula will only work in XL2007. In previous versions, you
can
not use a range like J:J, but something like J1:J1000.

Hopes this helps.

Per

Hi -

d text clipped - 18 lines Wrote:


Thanks for any help

--
Message posted via OfficeKB.com
'Excel - using Excel worksheet functions'
(http://www.officekb.com/Uwe/Forums.a...tions/200905/1)



--
Pecoflyer

Cheers -
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=92988