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

[Mifty]

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$5016=$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

 

[Mike H]

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

Mike

 

[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

 

[Mike H]

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]

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