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

[Guest]

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

 

[Guest]

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

 

[Guest]

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

Thanks,
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)

Then on your summary sheet:

=SUM(Januaryecember!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")))

Biff

 

[Guest]

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

 

[Guest]

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!

 

[Guest]

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

Cheri

 

[Biff]

that Indirect thing really threw me.

Ain't it a thing of beauty?

Thanks for the feedback!

Biff