SUMIF with AND

[vicvega]

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

[Biff]

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" <[email protected]> wrote in message
news:[email protected]...
>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
>

[vicvega]

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" <[email protected]> wrote in message
> news:[email protected]...
> >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
> >
>
>
>