From: Qaspec on 6 Apr 2010 11:30 I need to do some type of lookup and sum in vba I'd like to use Total Sheet.B5 to return the value Total Sheet.B5 to = 55 if Total Sheet.A5 = Orange Total Sheet A5 = Orange B5 = 55 Sheet 1 A4 = Blue b4 = 5 A5 = Green b5 = 10 A6 = Orange b6 = 15 Sheet 2 A4 = Orange b4 = 10 A5 = Green b5 = 20 A6 = Blue b6 = 30 Sheet 4 A4 = Green b4 = 20 A5 = Orange b5 = 30 A6 = Blue b6 = 40 Thank you for any help From: ryguy7272 on 6 Apr 2010 12:36 Hummm, I can't really tell what you're doing, but look here: http://www.xldynamic.com/source/xld.SUMPRODUCT.html I'm pretty confident that your answer is in there. -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Qaspec" wrote: > I need to do some type of lookup and sum in vba > I'd like to use Total Sheet.B5 to return the value > > Total Sheet.B5 to = 55 if Total Sheet.A5 = Orange > > > Total Sheet > A5 = Orange B5 = 55 > > Sheet 1 > A4 = Blue b4 = 5 > A5 = Green b5 = 10 > A6 = Orange b6 = 15 > > Sheet 2 > A4 = Orange b4 = 10 > A5 = Green b5 = 20 > A6 = Blue b6 = 30 > > > Sheet 4 > A4 = Green b4 = 20 > A5 = Orange b5 = 30 > A6 = Blue b6 = 40 > > Thank you for any help From: Andrew Taylor on 6 Apr 2010 12:42 in B5: =SUMIF(Sheet1!\$A\$4:\$A\$6,A5,Sheet1!\$B\$4:\$B\$6)+SUMIF(Sheet2!\$A\$4:\$A \$6,A5,Sheet2!\$B\$4:\$B\$6)+SUMIF(Sheet4!\$A\$4:\$A\$6,A5,Sheet4!\$B\$4:\$B\$6) is the best I can manage, though it gets ugly if you add more sheets. Unfortunately SUMIF doesn't work across multiple sheets, otherwise you could use =SUMIF(Sheet1:Sheet4!\$A\$4:\$A\$6,A5,Sheet1:Sheet4!\$B\$4:\$B\$6) but this gives #VALUE! On 6 Apr, 16:30, Qaspec wrote:> I need to do  some type of lookup and sum in vba > I'd like to use Total Sheet.B5 to return the value > > Total Sheet.B5 to = 55 if Total Sheet.A5 = Orange > > Total Sheet > A5 = Orange  B5 = 55 > > Sheet 1 > A4 = Blue            b4 = 5 > A5 = Green         b5 = 10 > A6 = Orange       b6 = 15 > > Sheet 2 > A4 = Orange      b4 = 10 > A5 = Green        b5 = 20 > A6 = Blue          b6 = 30 > > Sheet 4 > A4 = Green       b4 = 20 > A5 = Orange     b5 = 30 > A6 = Blue          b6 = 40   > > Thank you for any help  |  Pages: 1 Prev: prevent a user from adding any sheet in a workbookNext: Macro to Find Column