sumif/if with conditions "HELP"

[Guest]

Okay I think I'm making this harder than it should be. I need help!

I am making a summary sheet with several other sheets that hold data.
columns are the same in all the data sheets just the info is different by
part #, store #, store ranking, and qty. I am trying to make a summary page
that when I put this part # in this cell b1 on the summary sheet it will look
at all the data sheets find the part # and if it has this type ie. "A"
ranking in A12 on the summary sheet as well... then sumif the total in this
column "i4:i291" on each data sheet it finds. I need to have at least 7 Data
sheets. Because I have about 200 part # and 1000 stores.

This might not be the right formula to use. So, any help would be GREAT!

=IF(Data1!$A$4:$A$291=summary!$B$1,SUMIF(Data1!$G$4:$G$291,$A12,Data1!$I$4:$I$291)IF(Data2!$A$4:$A$291=summary!$B$1,SUMIF(Data2!$G$4:$G$291,$A12,Data2!$I$4:$I$291))

thanks so much, B.

 

[SteveG]

Not sure if this is what you are getting at but SUMPRODUCT could work.

=SUMPRODUCT((Data1!A4:A291=summary!B1)*(Data1!G4:G291=A12)*(Data1!1I4:I291))

and

=SUMPRODUCT((Data2!A4:A291=summary!B1)*(Data2!G4:G291=A12)*(Data2!1I4:I291))

Does that help?

Steve

 

[Guest]

I would probably try something like this:

=IF(ISNA(VLOOKUP(summary!$B$1,Data1!$A$4:$A$291,1,FALSE)),IF(ISNA(VLOOKUP(summary!$B$1,Data2!$A$4:$A$291,1,FALSE)),SUMIF(Data2!$G$4:$G$291,$A12,Data1!$I$4:$I$291),SUMIF(Data2!$G$4:$G$291,$A12,Data2!$I$4:$I$291)))

Obviously you would have to nest the IF statements up to 7 times which is
pushing the limits of excel but it should work.

Have you used Pivot Tables yet? A little tricky with multile sheets but it
is possible and much better for manipulating large data sets.