SUMIF with AND
 

I was trying to search several different sheets pull out some data that might
or might not be in all of the sheets and have it summed at the moment I am
using combo of Vlookups such as:

=((VLOOKUP(F16,A!$D$9:$J$1988,4,FALSE))+
(VLOOKUP(F16,B!$D$9:$J$1988,4,FALSE))+
(VLOOKUP(F16,'C'!$D$9:$J$1988,4,FALSE))+
(VLOOKUP(F16,D!$D$9:$J$1988,4,FALSE))+
(VLOOKUP(F16,E!$D$9:$J$1988,4,FALSE)))


Is there a way to get it to work using SUMIF I tried using sumif with using
AND in some of the arguments of the function with no luck,, any ideas would
be appreciated

SUMIF with AND
 

Hi!

Here's one way:

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"A","B","C","D","E "}&"'!D9:D1988"),F16,INDIRECT("'"&{"A","B","C","D" ,"E"}&"'!G9:G1988")))

Biff

"vicvega" wrote in message
...
I was trying to search several different sheets pull out some data that
might
or might not be in all of the sheets and have it summed at the moment I am
using combo of Vlookups such as:

=((VLOOKUP(F16,A!$D$9:$J$1988,4,FALSE))+
(VLOOKUP(F16,B!$D$9:$J$1988,4,FALSE))+
(VLOOKUP(F16,'C'!$D$9:$J$1988,4,FALSE))+
(VLOOKUP(F16,D!$D$9:$J$1988,4,FALSE))+
(VLOOKUP(F16,E!$D$9:$J$1988,4,FALSE)))


Is there a way to get it to work using SUMIF I tried using sumif with
using
AND in some of the arguments of the function with no luck,, any ideas
would
be appreciated

SUMIF with AND
 

It does the same thing for me, but the problem is it takes excel much longer
to calculate the cells creating lag/delay, this was something that I was
trying to reduce with using a different function, I probably should have
mentioned that in the first post,, thanks much for the effort though

"Biff" wrote:

Hi!

Here's one way:

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"A","B","C","D","E "}&"'!D9:D1988"),F16,INDIRECT("'"&{"A","B","C","D" ,"E"}&"'!G9:G1988")))

Biff

"vicvega" wrote in message
...
I was trying to search several different sheets pull out some data that
might
or might not be in all of the sheets and have it summed at the moment I am
using combo of Vlookups such as:

=((VLOOKUP(F16,A!$D$9:$J$1988,4,FALSE))+
(VLOOKUP(F16,B!$D$9:$J$1988,4,FALSE))+
(VLOOKUP(F16,'C'!$D$9:$J$1988,4,FALSE))+
(VLOOKUP(F16,D!$D$9:$J$1988,4,FALSE))+
(VLOOKUP(F16,E!$D$9:$J$1988,4,FALSE)))


Is there a way to get it to work using SUMIF I tried using sumif with
using
AND in some of the arguments of the function with no luck,, any ideas
would
be appreciated