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

[CheriT63]

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

[bpeltzer]

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

[CheriT63]

Thank you. I appreciate your assistance very much. But this formula
=SUMIF(January:December!C:C,"Savings",January:December!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

[Biff]

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"),"savings",INDIRECT("'"&J1:J12&"'!D:D")))

Biff

"CheriT63" <[email protected]> wrote in message
news:[email protected]...
> Thank you. I appreciate your assistance very much. But this formula
> =SUMIF(January:December!C:C,"Savings",January:December!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

[bpeltzer]

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:December!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

[CheriT63]

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:December!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

[CheriT63]

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"),"savings",INDIRECT("'"&J1:J12&"'!D:D")))
>
> Biff
>
> "CheriT63" <[email protected]> wrote in message
> news:[email protected]...
> > Thank you. I appreciate your assistance very much. But this formula
> > =SUMIF(January:December!C:C,"Savings",January:December!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
>
>
>

[Biff]

>that Indirect thing really threw me.

Ain't it a thing of beauty?

Thanks for the feedback!

Biff

"CheriT63" <[email protected]> wrote in message
news:[email protected]...
> 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"),"savings",INDIRECT("'"&J1:J12&"'!D:D")))
>>
>> Biff
>>
>> "CheriT63" <[email protected]> wrote in message
>> news:[email protected]...
>> > Thank you. I appreciate your assistance very much. But this formula
>> > =SUMIF(January:December!C:C,"Savings",January:December!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
>>
>>
>>