Simplify a complex formula

**Kosherboychief** · 06-16-2011, 05:49 PM

Hello!

I need help shortening this formula:

=SUM(SUMIF(INDIRECT({"'January","'February","'March","'April","'May","'June","'July","'August","'September","'October","'November","'December"}&"'!B:B"),$B7,INDIRECT({"'January","'February","'March","'April","'May","'June","'July","'August","'September","'October","'November","'December"}&"'!"&ADDRESS(1,COLUMN())&":"&ADDRESS(20000,COLUMN()))))

There are no gaps in between the sheets, and for the life of me I can't figure this out! The formula works in this form, it just....seems tacky? IDK a short formula is just so much smarter looking. No matter how hard I think, my brain is just coming back 404 whenever I try to figure out how to shorten it

Please help

**brokenbiscuits** · 06-17-2011, 03:50 AM

I haven't actually tested this, but I'd maybe expect the formula to be something along the lines of:

=SUMIF(January:December!B:B, B7,indirect("January:December!"&ADDRESS(1,COLUMN())&":"&ADDRESS(20000,COLUMN())))

?

**Kosherboychief** · 06-17-2011, 09:23 AM

Originally Posted by brokenbiscuits

I haven't actually tested this, but I'd maybe expect the formula to be something along the lines of:

=SUMIF(January:December!B:B, B7,indirect("January:December!"&ADDRESS(1,COLUMN())&":"&ADDRESS(20000,COLUMN())))

?

Sorry, no, it just comes back as #value!

Would it help if I attached a sample file?

**brokenbiscuits** · 06-17-2011, 09:26 AM

It can't hurt!

Give it a go, and we'll see.

**Kosherboychief** · 06-17-2011, 09:44 AM

The numbers are of course random

**daddylonglegs** · 06-17-2011, 09:50 AM

If you want to shorten by getting rid of the long "array constant" with the sheet names then one way is to list the sheet names somewhere on your sheet, e.g. in Z2:Z13, and then name that range Sheetlist and use this formula

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheetlist&"'!B:B"),$B7,INDIRECT("'"&Sheetlist&"'!"& ADDRESS(1,COLUMN())&":"&ADDRESS(20000,COLUMN()))))

alternatively you could use a formula to generate the month names, e.g.

=SUM(SUMIF(INDIRECT(TEXT({1,2,3,4,5,6,7,8,9,10,11,12}*29,"'mmmm'!")&"B:B"),$B7,INDIRECT(TEXT({1,2,3,4,5,6,7,8,9,10,11,12}*29,"'mmmm'!")&ADDRESS(1,COLUMN())&":"&ADDRESS(20000,COLUMN()))))

....and using the first version with a simplification of the ADDRESS part....

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheetlist&"'!B:B"),$B7,INDIRECT("'"&Sheetlist&"'!"& CELL("address",INDEX(1:20000,0,COLUMN())))))

**martindwilson** · 06-17-2011, 09:59 AM

create a named range of the months call it say "monthsheets"
then in c4 you can use
=SUMPRODUCT(SUMIF(INDIRECT("'"&monthsheets&"'!$B$4:$b$16"),B4,INDIRECT("'"&monthsheets&"'!$c$4:$c$16")))

**Kosherboychief** · 06-17-2011, 10:33 AM

@daddylonglegs, your

=SUM(SUMIF(INDIRECT(TEXT({1,2,3,4,5,6,7,8,9,10,11,12}*29,"'mmmm'!")&"B:B"),$B7,INDIRECT(TEXT({1,2,3, 4,5,6,7,8,9,10,11,12}*29,"'mmmm'!")&ADDRESS(1,COLUMN())&":"&ADDRESS(20000,COLUMN()))))

solution works the best so far (If I hide the cell that has the named range, my very OCD co-workers will notice, TRUST ME, and unhide it, which bugs me) however, I am having difficulty using the location parts of the formula in another formula from another book that I want to use to average the months which is
=IFERROR(=SUM(SUMIF(INDIRECT(TEXT({1,2,3,4,5,6,7,8,9,10,11,12}*29,"'mmmm'!")&"B:B"),$B7,INDIRECT(TEXT({1,2,3, 4,5,6,7,8,9,10,11,12}*29,"'mmmm'!")&ADDRESS(1,COLUMN())&":"&ADDRESS(20000,COLUMN()))))/SUMPRODUCT(COUNTIFS(INDIRECT("'Week "&{1,2,3,4,5}&"'!A1:A1000"),$A6,INDIRECT("'Week "&{1,2,3,4,5}&"'!"&ADDRESS(1,COLUMN())&":"&ADDRESS(1000,COLUMN())),">0")),"")

Any tips?

**martindwilson** · 06-17-2011, 11:21 AM

fyi you dont have to put months in a range of cells to create a named range you can just create a named range of

={"january";"february";"march";"april";"may";"june";"july";"august";"september";"october";"november";"december"}

**Kosherboychief** · 06-17-2011, 11:40 AM

Originally Posted by martindwilson

fyi you dont have to put months in a range of cells to create a named range you can just create a named range of

This actually causes it to return an incorrect sum

**martindwilson** · 06-17-2011, 11:47 AM

no it doesnt
it gives same values you have see attached named range is called "testing"

**Kosherboychief** · 06-17-2011, 11:55 AM

My bad, I must have done my test incorrectly, your formula works well. How do you think I could use it to average?

**martindwilson** · 06-17-2011, 07:26 PM

dont you just divide total by 12?

**Kosherboychief** · 06-18-2011, 03:40 AM

Originally Posted by martindwilson

dont you just divide total by 12?

If all the values were filled in for all the months, then yes, if they are not, it will divide by too many.

Ex. Average of 10,10,10,10,10 is 10, but if, lets say we are using our formulas from above then the 10+10+10+10+10, instead of being divided by 5 (because there are 5 numbers being averaged) would be divided by, say, 8 is 6.25
Average means sum/however many numbers you are summing, and for this problem, the formula needs to be able to detect and ignore zeros and blanks

**daddylonglegs** · 06-18-2011, 08:22 AM

You should be able to do it as you tried, e.g. using Martin's suggested formula for the sum then the average would be

=SUMPRODUCT(SUMIF(INDIRECT("'"&monthsheets&"'!B4:B16"),B4,INDIRECT("'"&monthsheets&"'!C4:C16")))/SUMPRODUCT(COUNTIFS(INDIRECT("'"&monthsheets&"'!B4:B16"),B4,INDIRECT("'"&monthsheets&"'!C4:C16"),">0"))

**Kosherboychief** · 06-20-2011, 09:34 AM

Thanks daddylonglegs and martindwilson, I learned a bunch and the problem was solved!

Simplify a complex formula

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Simplify a complex formula

Re: Simplify a complex formula

Re: Simplify a complex formula

Re: Simplify a complex formula

Re: Simplify a complex formula

Re: Simplify a complex formula

Re: Simplify a complex formula

Re: Simplify a complex formula

Re: Simplify a complex formula

Re: Simplify a complex formula

Re: Simplify a complex formula

Re: Simplify a complex formula

Re: Simplify a complex formula [NOT SOLVED :( ]

Re: Simplify a complex formula [NOT SOLVED :( ]

Re: Simplify a complex formula [NOT SOLVED :( ]

Re: Simplify a complex formula

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions