Formula Question

Q

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
 
A

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top