Using SUMIF across several spreadsheets

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I'm trying to add specific values accros several
spreadsheets.

Example

sheet 1

a1=monday, b1=10
a2=wednesday, b2=30
a3=friday, b3=50

sheet 2

a1=wednesday, b1=5
a2=monday, b1=3


I'm trying to return the sum value on a third sheet

monday=10+3=13
wednesday=30+5=35
friday=50

Can anyone help. Not sure if I should be using SUMIF
 
One possible way

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&{1,2}&"'!A1:A10"),"Monday",INDIRECT("'Sh
eet"&{1,2}&"'!B1:B10")))

now you might have different sheet names than in your example,
you could put all the sheet names in a range and reference the range (either
by giving it a name)
or the cell references

=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:A2&"'!A1:A10"),"Monday",INDIRECT("'"&A1:A2
&"'!B1:B10")))

and with a defined name

=SUMPRODUCT(SUMIF(INDIRECT("'"&MySheets&"'!A1:A10"),"Monday",INDIRECT("'"&My
Sheets&"'!B1:B10")))
 
Jim said:
I'm trying to add specific values accros several
spreadsheets.

Example

sheet 1

a1=monday, b1=10
a2=wednesday, b2=30
a3=friday, b3=50

sheet 2

a1=wednesday, b1=5
a2=monday, b1=3


I'm trying to return the sum value on a third sheet

monday=10+3=13
wednesday=30+5=35
friday=50

Can anyone help. Not sure if I should be using SUMIF
Click to expand...

Enter into Sheet3!A1:A7, Monday, Tuesday, etc. This can be done with
the fill handle. Then enter the following formula into Sheet3!B1 and
copy down to B7.

=SUMIF(Sheet1!$A$1:$A$10,Sheet3!A1,Sheet1!$B$1:$B$10)
+SUMIF(Sheet2!$A$1:$A$10,Sheet3!A1,Sheet2!$B$1:$B$10)

Allan Rogg
 
Immanuel,

Thanks you for your reply.
Your solution is great for the problem I communicated.
Unfortunately, I should of stated I was trying to
incorporate multiple criterias.

Example if we added a third column with values say day or
night shift.

Do you have any thoughts?

Jim
 
Peo,

Thanks you for your reply.
Your solution is great for the problem I communicated.
Unfortunately, I should of stated I was trying to
incorporate multiple criterias.

Example if we added a third column with values say day or
night shift.

Do you have any thoughts?

Jim
 
...
...
Thanks you for your reply.
Your solution is great for the problem I communicated.
Unfortunately, I should of stated I was trying to
incorporate multiple criterias.

Example if we added a third column with values say day or
night shift. ...
Click to expand...
...

Can't be done without add-in functions or user-defined functions. The best
approach would be to use Laurent Longre's MOREFUNC.XLL add-in, available from

http://longre.free.fr/english

It contains a function named THREED, which takes 3D references and returns
stacked 2D arrays from them. For example,

Sheet1!A1:C4
a b c
d e f
g h i
j k l

Sheet2!A1:C4
m n o
p q r
s t u
v w x

THREED(Sheet1:Sheet2!A1:C4) returns the 8-by-3 array

a b c
d e f
g h i
j k l
m n o
p q r
s t u
v w x

You'd use THREED in SUMPRODUCT formulas like so

=SUMPRODUCT(THREED(Sheet1:Sheet4!C1:C20)
*(THREED(Sheet1:Sheet4!A1:A20)="Monday")
*(THREED(Sheet1:Sheet4!B1:B20)="Night"))

to sum entries from column C where the corresponding entries in columns A and B
match certain criteria.
 
If the values "Day" and "Night" appear in column C of Sheet1 and
Sheet2, you might be able to use a SUMPRODUCT formula like this:

=SUMPRODUCT((Sheet1!A1:A10="Monday")*(Sheet1!C1:C10="Day"),Sheet1!B1:B10)
+SUMPRODUCT((Sheet2!A1:A10="Monday")*(Sheet2!C1:C10="Day"),Sheet2!B1:B10)

You might want to put the possible values for columns A and C in a
table and use cell references rather than spelling them out in the
formulas. This style of formula will also work for more than two
criteria. Just multiply together as many logical conditions as
necessary.

Allan Rogg
 
Thanks Allan it works great.

-----Original Message-----
If the values "Day" and "Night" appear in column C of Sheet1 and
Sheet2, you might be able to use a SUMPRODUCT formula like this:

=SUMPRODUCT((Sheet1!A1:A10="Monday")*(Sheet1! C1:C10="Day"),Sheet1!B1:B10)
C1:C10="Day"),Sheet2!B1:B10)

You might want to put the possible values for columns A and C in a
table and use cell references rather than spelling them out in the
formulas. This style of formula will also work for more than two
criteria. Just multiply together as many logical conditions as
necessary.

Allan Rogg

"Jim" <[email protected]> wrote in message
.
Click to expand...
 
Back
Top