Sum values in multiple sheets using Lookup to find a text match
 

I am trying to create a formula that will allow me to look at multiple
spreadsheets, find text and sum all values it finds.

I have a budget set up on 12 spreadsheets from Jan to Dec. I am looking for
a formula that will allow me to lookup anywhere I have input SAVINGS and sum
the amounts listed in the subsequent column where I have input the savings
amount for that week.

Help!

Cheri

Sum values in multiple sheets using Lookup to find a text match
 

Check the SUMIF function instead of lookup. The general format is
=sumif(where to look, what to look for, what to add when matching). So if
you want to find "SAVINGS" in sheet1 column C and add up all the
corresponding values in sheet1 column B, it would be
=sumif(Sheet1!C:C,"SAVINGS",Sheet1!B:B). You can change sheet names and
columns as needed, and add up the results of 12 such sumif's to capture all
12 months.
You can also limit the ranges to specific rows
(Sheet1!$C$1:$C$C20,"SAVINGS",Sheet1!$B$1:$B$20). But unless you have other
data in those columns that could get inadvertently added in, I find it easier
to use entire columns; it also avoids the problem adding rows that extend
beyond the range used in the sumif function.
HTH. --Bruce

"CheriT63" wrote:

I am trying to create a formula that will allow me to look at multiple
spreadsheets, find text and sum all values it finds.

I have a budget set up on 12 spreadsheets from Jan to Dec. I am looking for
a formula that will allow me to lookup anywhere I have input SAVINGS and sum
the amounts listed in the subsequent column where I have input the savings
amount for that week.

Help!

Cheri

Sum values in multiple sheets using Lookup to find a text matc
 

Thank you. I appreciate your assistance very much. But this formula
=SUMIF(January:December!C:C,"Savings",January:Dece mber!D:D) returned the
infamous #VALUE! error. Can you tell me what I may have done wrong?

Thanks,
Cheri


"bpeltzer" wrote:

Check the SUMIF function instead of lookup. The general format is
=sumif(where to look, what to look for, what to add when matching). So if
you want to find "SAVINGS" in sheet1 column C and add up all the
corresponding values in sheet1 column B, it would be
=sumif(Sheet1!C:C,"SAVINGS",Sheet1!B:B). You can change sheet names and
columns as needed, and add up the results of 12 such sumif's to capture all
12 months.
You can also limit the ranges to specific rows
(Sheet1!$C$1:$C$C20,"SAVINGS",Sheet1!$B$1:$B$20). But unless you have other
data in those columns that could get inadvertently added in, I find it easier
to use entire columns; it also avoids the problem adding rows that extend
beyond the range used in the sumif function.
HTH. --Bruce

"CheriT63" wrote:

I am trying to create a formula that will allow me to look at multiple
spreadsheets, find text and sum all values it finds.

I have a budget set up on 12 spreadsheets from Jan to Dec. I am looking for
a formula that will allow me to lookup anywhere I have input SAVINGS and sum
the amounts listed in the subsequent column where I have input the savings
amount for that week.

Help!

Cheri

Sum values in multiple sheets using Lookup to find a text matc
 

Hi!

Best practice:

Put a Sumif formula on each sheet in the same cell:

A1 of each sheet:

=SUMIF(C:C,"savings",D:D)

Then on your summary sheet:

=SUM(January:December!A1)

Another way to do it in one shot:

On your summary sheet make a list of the sheet names:

J1 = January
J2 = February
J3 = March
...
J12 = December

Then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&J1:J12&"'!C:C"),"sa vings",INDIRECT("'"&J1:J12&"'!D:D")))

Biff

"CheriT63" wrote in message
...
Thank you. I appreciate your assistance very much. But this formula
=SUMIF(January:December!C:C,"Savings",January:Dece mber!D:D) returned the
infamous #VALUE! error. Can you tell me what I may have done wrong?

Thanks,
Cheri


"bpeltzer" wrote:

Check the SUMIF function instead of lookup. The general format is
=sumif(where to look, what to look for, what to add when matching). So
if
you want to find "SAVINGS" in sheet1 column C and add up all the
corresponding values in sheet1 column B, it would be
=sumif(Sheet1!C:C,"SAVINGS",Sheet1!B:B). You can change sheet names and
columns as needed, and add up the results of 12 such sumif's to capture
all
12 months.
You can also limit the ranges to specific rows
(Sheet1!$C$1:$C$C20,"SAVINGS",Sheet1!$B$1:$B$20). But unless you have
other
data in those columns that could get inadvertently added in, I find it
easier
to use entire columns; it also avoids the problem adding rows that
extend
beyond the range used in the sumif function.
HTH. --Bruce

"CheriT63" wrote:

I am trying to create a formula that will allow me to look at multiple
spreadsheets, find text and sum all values it finds.

I have a budget set up on 12 spreadsheets from Jan to Dec. I am
looking for
a formula that will allow me to lookup anywhere I have input SAVINGS
and sum
the amounts listed in the subsequent column where I have input the
savings
amount for that week.

Help!

Cheri

Sum values in multiple sheets using Lookup to find a text matc
 

Use a separate SUMIF for each sheet: =sumif(Jan!c:c,"Savings",Jan!d:d) +
sumif(Feb!c:c,"Savings",Feb!d:d) + ...


"CheriT63" wrote:

Thank you. I appreciate your assistance very much. But this formula
=SUMIF(January:December!C:C,"Savings",January:Dece mber!D:D) returned the
infamous #VALUE! error. Can you tell me what I may have done wrong?

Thanks,
Cheri


"bpeltzer" wrote:

Check the SUMIF function instead of lookup. The general format is
=sumif(where to look, what to look for, what to add when matching). So if
you want to find "SAVINGS" in sheet1 column C and add up all the
corresponding values in sheet1 column B, it would be
=sumif(Sheet1!C:C,"SAVINGS",Sheet1!B:B). You can change sheet names and
columns as needed, and add up the results of 12 such sumif's to capture all
12 months.
You can also limit the ranges to specific rows
(Sheet1!$C$1:$C$C20,"SAVINGS",Sheet1!$B$1:$B$20). But unless you have other
data in those columns that could get inadvertently added in, I find it easier
to use entire columns; it also avoids the problem adding rows that extend
beyond the range used in the sumif function.
HTH. --Bruce

"CheriT63" wrote:

I am trying to create a formula that will allow me to look at multiple
spreadsheets, find text and sum all values it finds.

I have a budget set up on 12 spreadsheets from Jan to Dec. I am looking for
a formula that will allow me to lookup anywhere I have input SAVINGS and sum
the amounts listed in the subsequent column where I have input the savings
amount for that week.

Help!

Cheri

Sum values in multiple sheets using Lookup to find a text matc
 

Hi Bruce,

Thanks! I did that and it did work. It just seemed a bit cumbersome. I
would think that there is an easier way to do this in Excel. Maybe not.

Thanks again!

"bpeltzer" wrote:

Use a separate SUMIF for each sheet: =sumif(Jan!c:c,"Savings",Jan!d:d) +
sumif(Feb!c:c,"Savings",Feb!d:d) + ...


"CheriT63" wrote:

Thank you. I appreciate your assistance very much. But this formula
=SUMIF(January:December!C:C,"Savings",January:Dece mber!D:D) returned the
infamous #VALUE! error. Can you tell me what I may have done wrong?

Thanks,
Cheri


"bpeltzer" wrote:

Check the SUMIF function instead of lookup. The general format is
=sumif(where to look, what to look for, what to add when matching). So if
you want to find "SAVINGS" in sheet1 column C and add up all the
corresponding values in sheet1 column B, it would be
=sumif(Sheet1!C:C,"SAVINGS",Sheet1!B:B). You can change sheet names and
columns as needed, and add up the results of 12 such sumif's to capture all
12 months.
You can also limit the ranges to specific rows
(Sheet1!$C$1:$C$C20,"SAVINGS",Sheet1!$B$1:$B$20). But unless you have other
data in those columns that could get inadvertently added in, I find it easier
to use entire columns; it also avoids the problem adding rows that extend
beyond the range used in the sumif function.
HTH. --Bruce

"CheriT63" wrote:

I am trying to create a formula that will allow me to look at multiple
spreadsheets, find text and sum all values it finds.

I have a budget set up on 12 spreadsheets from Jan to Dec. I am looking for
a formula that will allow me to lookup anywhere I have input SAVINGS and sum
the amounts listed in the subsequent column where I have input the savings
amount for that week.

Help!

Cheri

Sum values in multiple sheets using Lookup to find a text matc
 

Thanks for the help! Wow, that Indirect thing really threw me. I think I
will stick with your other suggestion. :o)

Cheri

"Biff" wrote:

Hi!

Best practice:

Put a Sumif formula on each sheet in the same cell:

A1 of each sheet:

=SUMIF(C:C,"savings",D:D)

Then on your summary sheet:

=SUM(January:December!A1)

Another way to do it in one shot:

On your summary sheet make a list of the sheet names:

J1 = January
J2 = February
J3 = March
...
J12 = December

Then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&J1:J12&"'!C:C"),"sa vings",INDIRECT("'"&J1:J12&"'!D:D")))

Biff

"CheriT63" wrote in message
...
Thank you. I appreciate your assistance very much. But this formula
=SUMIF(January:December!C:C,"Savings",January:Dece mber!D:D) returned the
infamous #VALUE! error. Can you tell me what I may have done wrong?

Thanks,
Cheri


"bpeltzer" wrote:

Check the SUMIF function instead of lookup. The general format is
=sumif(where to look, what to look for, what to add when matching). So
if
you want to find "SAVINGS" in sheet1 column C and add up all the
corresponding values in sheet1 column B, it would be
=sumif(Sheet1!C:C,"SAVINGS",Sheet1!B:B). You can change sheet names and
columns as needed, and add up the results of 12 such sumif's to capture
all
12 months.
You can also limit the ranges to specific rows
(Sheet1!$C$1:$C$C20,"SAVINGS",Sheet1!$B$1:$B$20). But unless you have
other
data in those columns that could get inadvertently added in, I find it
easier
to use entire columns; it also avoids the problem adding rows that
extend
beyond the range used in the sumif function.
HTH. --Bruce

"CheriT63" wrote:

I am trying to create a formula that will allow me to look at multiple
spreadsheets, find text and sum all values it finds.

I have a budget set up on 12 spreadsheets from Jan to Dec. I am
looking for
a formula that will allow me to lookup anywhere I have input SAVINGS
and sum
the amounts listed in the subsequent column where I have input the
savings
amount for that week.

Help!

Cheri

Sum values in multiple sheets using Lookup to find a text matc
 

that Indirect thing really threw me.

Ain't it a thing of beauty?

Thanks for the feedback!

Biff

"CheriT63" wrote in message
...
Thanks for the help! Wow, that Indirect thing really threw me. I think I
will stick with your other suggestion. :o)

Cheri

"Biff" wrote:

Hi!

Best practice:

Put a Sumif formula on each sheet in the same cell:

A1 of each sheet:

=SUMIF(C:C,"savings",D:D)

Then on your summary sheet:

=SUM(January:December!A1)

Another way to do it in one shot:

On your summary sheet make a list of the sheet names:

J1 = January
J2 = February
J3 = March
...
J12 = December

Then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&J1:J12&"'!C:C"),"sa vings",INDIRECT("'"&J1:J12&"'!D:D")))

Biff

"CheriT63" wrote in message
...
Thank you. I appreciate your assistance very much. But this formula
=SUMIF(January:December!C:C,"Savings",January:Dece mber!D:D) returned
the
infamous #VALUE! error. Can you tell me what I may have done wrong?

Thanks,
Cheri


"bpeltzer" wrote:

Check the SUMIF function instead of lookup. The general format is
=sumif(where to look, what to look for, what to add when matching).
So
if
you want to find "SAVINGS" in sheet1 column C and add up all the
corresponding values in sheet1 column B, it would be
=sumif(Sheet1!C:C,"SAVINGS",Sheet1!B:B). You can change sheet names
and
columns as needed, and add up the results of 12 such sumif's to
capture
all
12 months.
You can also limit the ranges to specific rows
(Sheet1!$C$1:$C$C20,"SAVINGS",Sheet1!$B$1:$B$20). But unless you have
other
data in those columns that could get inadvertently added in, I find it
easier
to use entire columns; it also avoids the problem adding rows that
extend
beyond the range used in the sumif function.
HTH. --Bruce

"CheriT63" wrote:

I am trying to create a formula that will allow me to look at
multiple
spreadsheets, find text and sum all values it finds.

I have a budget set up on 12 spreadsheets from Jan to Dec. I am
looking for
a formula that will allow me to lookup anywhere I have input SAVINGS
and sum
the amounts listed in the subsequent column where I have input the
savings
amount for that week.

Help!

Cheri