Hi there,

Can anyone help me to get this formula to work please? I want to count
numbers of passes from two different worksheets "All" and "Bob"

=SUMPRODUCT((All!$A$2:$A$5016=$A9)*(Bob!$A$2:$A$50 16=$A9)*(All!$K$2:$K$5016=4)*(Bob!$I$2:$I$2016=4)* (All!$F$2:$F$5016<=$D$4)*(Bob!$F$2:$F$2016<=$D$4))

I get #N/A

Grateful for any help
Cheers
--
Mifty

It's because your ranges are different sizes 2016 & 5016 set them all the
same and your formula works

Mike

"Mifty" wrote:

Hi there,

Can anyone help me to get this formula to work please? I want to count
numbers of passes from two different worksheets "All" and "Bob"

=SUMPRODUCT((All!$A$2:$A$5016=$A9)*(Bob!$A$2:$A$50 16=$A9)*(All!$K$2:$K$5016=4)*(Bob!$I$2:$I$2016=4)* (All!$F$2:$F$5016<=$D$4)*(Bob!$F$2:$F$2016<=$D$4))

I get #N/A

Grateful for any help
Cheers
--
Mifty

Hi Mike,

Thanks for answering but I still must be doing something wrong 'cos all I
get is zero even when I know there should be a number there.

Cheers
--
Mifty


"Mike H" wrote:

It's because your ranges are different sizes 2016 & 5016 set them all the
same and your formula works

Mike

"Mifty" wrote:

Hi there,

Can anyone help me to get this formula to work please? I want to count
numbers of passes from two different worksheets "All" and "Bob"

=SUMPRODUCT((All!$A$2:$A$5016=$A9)*(Bob!$A$2:$A$50 16=$A9)*(All!$K$2:$K$5016=4)*(Bob!$I$2:$I$2016=4)* (All!$F$2:$F$5016<=$D$4)*(Bob!$F$2:$F$2016<=$D$4))

I get #N/A

Grateful for any help
Cheers
--
Mifty

Given that it works for me with the ranges corrected then it must be your
data. I don't know for example what you have in A49 but if its a number then
ensure you have numbers in the columns you are checking.

One way to check is to shorten the ranges 9(ll of them) for de-bugging then
select each part of your formula in the formula bar and Tap F9

select this bit Bob!$I$2:$I$20=4

With this selected you will see the formula bar change to TRUE or FALSE to
show you how each of the 20 cells is evaluating.

Mike

"Mifty" wrote:

Hi Mike,

Thanks for answering but I still must be doing something wrong 'cos all I
get is zero even when I know there should be a number there.

Cheers
--
Mifty


"Mike H" wrote:

It's because your ranges are different sizes 2016 & 5016 set them all the
same and your formula works

Mike

"Mifty" wrote:

Hi there,

Can anyone help me to get this formula to work please? I want to count
numbers of passes from two different worksheets "All" and "Bob"

=SUMPRODUCT((All!$A$2:$A$5016=$A9)*(Bob!$A$2:$A$50 16=$A9)*(All!$K$2:$K$5016=4)*(Bob!$I$2:$I$2016=4)* (All!$F$2:$F$5016<=$D$4)*(Bob!$F$2:$F$2016<=$D$4))

I get #N/A

Grateful for any help
Cheers
--
Mifty

Hi Mike,

Thanks for your help which had made me realise that I've got all sorts of
problems with data not formatted etc. Data has been imported from a custom
database using a wizard to dump into Excel. Also my summary sheet formatting
is all over the place. copied someone else's to save time but didn't think
about formatting so got a big headache.

Will try your solution when sorted
--
Mifty


"Mike H" wrote:

Given that it works for me with the ranges corrected then it must be your
data. I don't know for example what you have in A49 but if its a number then
ensure you have numbers in the columns you are checking.

One way to check is to shorten the ranges 9(ll of them) for de-bugging then
select each part of your formula in the formula bar and Tap F9

select this bit Bob!$I$2:$I$20=4

With this selected you will see the formula bar change to TRUE or FALSE to
show you how each of the 20 cells is evaluating.

Mike

"Mifty" wrote:

Hi Mike,

Thanks for answering but I still must be doing something wrong 'cos all I
get is zero even when I know there should be a number there.

Cheers
--
Mifty


"Mike H" wrote:

It's because your ranges are different sizes 2016 & 5016 set them all the
same and your formula works

Mike

"Mifty" wrote:

Hi there,

Can anyone help me to get this formula to work please? I want to count
numbers of passes from two different worksheets "All" and "Bob"

=SUMPRODUCT((All!$A$2:$A$5016=$A9)*(Bob!$A$2:$A$50 16=$A9)*(All!$K$2:$K$5016=4)*(Bob!$I$2:$I$2016=4)* (All!$F$2:$F$5016<=$D$4)*(Bob!$F$2:$F$2016<=$D$4))

I get #N/A

Grateful for any help
Cheers
--
Mifty