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

Hummm, I can't really tell what you're doing, but look he
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

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