oh, and another question if it's ok, in the INDEX i notice you're detailed the first and last rows of the range "2" and "10".
what if the last row of the range was unknown? could you include an OFFSET function to determine the "10" in the second INDEX function and use a named dynamic range for "A2:A10" etc?
As long as you put your summaries on the side and you don't have other non-relevant data below your database...then you can use this formula to make the last row dynamic (see attached):
To understand the formula, try performing a formula audit by going to Tools|Formula Auditing|Evaluate Formula and then keep clicking Evaluate to step through the evaluation process...
=SUMIF($A$2:$A$10,$H2,INDEX($A$1:$F$10,2,MATCH(I$1,$A$1:$F$1,0)):INDEX($A$1:$F$10,MATCH(REPT("z",255),$A:$A),MATCH(I$1,$A$1:$F$1,0)))
If you still have specific questions, please post them back.
Hope this helps
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
No problem, I should have read your question properly. Looks like you're sorted now but not completely sure why SUMPRODUCT wouldn't work.
Actually, revisiting my last post, I realized, there are more places you need to add the end row dynamic finding.....
changed the formula to:
=SUMIF($A$2:INDEX($A:$A,MATCH(REPT("z",255),$A:$A)),$H2,INDEX($A$1:INDEX($F:$F,MATCH(REPT("z",255),$A:$A)),2,MATCH(I$1,$A$1:$F$1,0)):INDEX($A$1:$F$10,MATCH(REPT("z",255),$A:$A),MATCH(I$1,$A$1:$F$1,0)))
thanks NBVC. i'll try that today.
NBVC, i just wanted to say thanks for pointing me in the right direction with your formula.
since i could follow your suggestion, and it seemed to resolve better than some other SUMIF solutions i've seen on the net i was able to figure out how to apply it to my actual situation.
happy new year everyone!
i guess 2008 continues to bring us all Excel questions...
i've discovered an issue in the attached formula and can't seem to resolve it. to refresh, what needs to occur is as follows. data is contained in the range A1:G12. this needs to be summarised into groups as suggested by range A15:G18. not all "Ps" will be present in the data range, yet all Ps are mapped to their higher level Group in range A22:B28.
yet in the attached suggestion i can not see how the formula refers to the headings in Row 1? Also, there are no "P" items allocated to Group3 yet there are summary results. similarly Group4 should equal the P4 items yet it equals the P7 items.
can anyone help?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks