Formula Question
in [Programming]
|
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 <Qas...(a)discussions.microsoft.com> 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 workbook Next: Macro to Find Column |