Sumif

[Stewy p]

Hello!

I'm trying to use a sumif function to add up multiple worksheets.

To look up column A (form) and compare to a2 (in master worksheet) an
sum column E (forecast).

e.g. I have eight worksheets - region 1 to region 8 and sum everythin
into master worksheet. The following formula gives me #Value!.

=SUMIF('region 1:region 8'!A2:A2000,A2,'region 1:region 8'!E2:E2000)

All worksheets are setup like this.

form, supplier form, material, description, forecast.....


I've checked all my data - sorted properly and column e is all number
in all worksheets. Is there a way to do it

 

[Peo Sjoblom]

Sumif does not work over multiple sheets but there are workarounds

=SUMPRODUCT(SUMIF(INDIRECT("'Region
"&ROW(INDIRECT("1:8"))&"'!A2:A2000"),A2,INDIRECT("'Region
"&ROW(INDIRECT("1:8"))&"'!E2:E2000")))

should work

 

[Peo Sjoblom]

I noticed the line wraps and if the formula wraps after Region, make sure
there is a space after region
like

"'Region "&ROW(INDIRECT("1:8"))

--

Regards,

Peo Sjoblom

Sumif does not work over multiple sheets but there are workarounds

=SUMPRODUCT(SUMIF(INDIRECT("'Region
"&ROW(INDIRECT("1:8"))&"'!A2:A2000"),A2,INDIRECT("'Region
"&ROW(INDIRECT("1:8"))&"'!E2:E2000")))

should work

 

[Don Guillett]

this might help for the 3 line formula

Sub FixLongFormulas() 'goto a remote area of ws & select 1st line
x = ActiveCell.Row
y = ActiveCell.Column
z = ActiveCell.End(xlDown).Row
For Each C In Range(Cells(x, y), Cells(z, y))
'Cells(x - 1, y) = Cells(x - 1, y) & C
mstr = mstr & C
Next
Cells(x - 1, y) = mstr
End Sub

--
Don Guillett
SalesAid Software
(e-mail address removed)

I noticed the line wraps and if the formula wraps after Region, make sure
there is a space after region
like

"'Region "&ROW(INDIRECT("1:8"))