SUMPRODUCT can I use to get data from 2 worksheets in same wkBK
 

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

SUMPRODUCT can I use to get data from 2 worksheets in same wkBK
 

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

SUMPRODUCT can I use to get data from 2 worksheets in same wkB
 

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

SUMPRODUCT can I use to get data from 2 worksheets in same wkB
 

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

SUMPRODUCT can I use to get data from 2 worksheets in same wkB
 

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