Sumif

  • Thread starter Thread starter Stewy p
  • Start date Start date
S

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
 
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
 
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

Peo Sjoblom said:
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
Click to expand...
 
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)
Peo Sjoblom said:
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"))
Click to expand...
 
Back
Top