Formula Question

[Qaspec]

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

 

[ryguy7272]

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.

 

[Andrew Taylor]

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!