Hello!
I need help shortening this formula:
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 Login or Register to view this content.
Please help
Last edited by Kosherboychief; 06-20-2011 at 09:35 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())))
?
Sorry, no, it just comes back as #value!Originally Posted by brokenbiscuits
Would it help if I attached a sample file?
It can't hurt!
Give it a go, and we'll see.
The numbers are of course random
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())))))
Last edited by daddylonglegs; 06-17-2011 at 09:54 AM.
Audere est facere
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")))
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
@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?
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"}
This actually causes it to return an incorrect sum
no it doesnt
it gives same values you have see attached named range is called "testing"
My bad, I must have done my test incorrectly, your formula works well. How do you think I could use it to average?
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
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"))
Thanks daddylonglegs and martindwilson, I learned a bunch and the problem was solved!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks