More than one variable for Countif

[Carrie_Loos via OfficeKB.com]

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

 

[Alan]

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

 

[Per Jessen]

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

 

[Pecoflyer]

Hi,

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

HTH

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

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

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.

 

[Carrie_Loos via OfficeKB.com]

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..........?

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

 

[Pecoflyer]

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..........?


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

 

[Bassman62]

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]

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

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

 

[Alan]

Use the formula supplied by Bassman, it will work,

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

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

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

 

[Bassman62]

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

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

 

[Carrie_Loos via OfficeKB.com]

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

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]

 

[Carrie_Loos via OfficeKB.com]

Worked beautifully, Thanks

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]

 

[Carrie_Loos via OfficeKB.com]

Worked beautifully, Thanks

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]

 

[Carrie_Loos via OfficeKB.com]

Worked beautifully, Thanks

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]

 

[Pecoflyer]

Have a look at this site
http://www.xldynamic.com/source/xld.SUMPRODUCT.html#classic for a
detailed explanation of the sumproduct syntax
HTH

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.