I think I need a LOOKUP, but have gone a bit rusty!

[cedricthecat]

This is my first post in ages!

Before I was made redundant about three years ago, I was totally immersed in Excel for most of the time at work, but since then, have become a rusty to say the least! I still maintain a few Workbooks for my own personal use, but nothing overly complicated, so the skills are waning.....

Anyway, would appreciate if someone could point me in the right direction on this!

Trying to sort a Workbook out for a friend. It's for expenses and has Worksheets for January 2012, February 2015 etc and one called "Average". The monthly ones have a descriptor in Column F, with a £ figure in Column D, starting on Row 2 through to Row 14, so F2:F14 are the descriptors, D2:D14 are the monetary figures. In Worksheet "Average", the descriptors are in E2:E14, with C2:C14 being totals for each item across each month

What I'm needing is something to go in C on "Average" to total these up. Currently we have

=SUM('January 2015'!D2+'February 2015'!D2+'March 2015'!D2+'April 2015'!D2+'May 2015'!D2+'June 2015'!D2)
etc

But we need something a little more sophisticated, as although the descriptors (things like "Energy Bill", "Council Tax" etc), are the same for each month, the don't always follow the same order, as they appear in each monthly Sheet in date order!

So, I was trying to work out a way on "Average" to LOOKUP the value of E2, find it's position in F2:F14 in each monthly sheet, return the corresponding value in D2:D14, and then SUM them.

Now, I'm guessing that this is probably not too complex, but sadly, trying to use a MATCH LOOKUP formula, I am just tying myself up in knots!

Any help would be very much appreciated! :D

 

[rilzniak]

Averageifs?

 

[konew1]

You might be able to do this with UDF or VBA, but as an alternative you could include a summary on each month data. If each month normally has 50 rows then go to somewhere like row 100 and put in a listing of all the descriptors in a standard sequence and use =sumif() to create a panel to use on average sheet.
You can then use consolidation formula on Average sheet. Use format like =SUM(Jan:Mar!D2) or =AVERAGE(Jan:Mar!D2) changing the sheet names as required

 

[cedricthecat]

Averageifs?

Could be wrong, but don't think my version of Excel (2003) has this function! (I only use it at home now and can't afford/justify an upgrade!)

 

[cedricthecat]

You might be able to do this with UDF or VBA, but as an alternative you could include a summary on each month data. If each month normally has 50 rows then go to somewhere like row 100 and put in a listing of all the descriptors in a standard sequence and use =sumif() to create a panel to use on average sheet.
You can then use consolidation formula on Average sheet. Use format like =SUM(Jan:Mar!D2) or =AVERAGE(Jan:Mar!D2) changing the sheet names as required

!
Sounds like a possible plan! Thanks for the idea

 

[konew1]

Could be wrong, but don't think my version of Excel (2003) has this function! (I only use it at home now and can't afford/justify an upgrade!)

=Averageifs() function is not in xl2003, but it would not neatly access data from multiple monthly sheets.

I am not sure if the consolidate function is available to you, either, so some changes may be required.

 

[cedricthecat]

Thanks again - looks like a re-think of layout might be on the cards!

 

[cedricthecat]

Well, in an attempt to simplify this, I ended up with a "Helper Sheet" which collates all the categories and their values, and then added an alphabetical list below this of all the categories (turned out to be just 14) which in term pulls the figures which match each category. So, like this

=INDEX(B$2:B$15,MATCH(A19,A$2:A$15,0))

I'm happy with how it works, but just one thing has struck me: In some of the monthly sheets, there's some items that appear twice, for example "Monthly ATM". Is there a way to cater for this within the existing formula, or do I need another re-think to condense duplicates down (basically add them) before using my formula?

As always, any advice and suggestions appreciated!

 

[konew1]

With items appearing more than once you will need to use =SUMIF() or =SUMPRODUCT(). These are both available in excel 2003
=sumif(A$2:A$15,A19,B$2:B$15)
=sumproduct(--(A$2:A$15=A19),B$2:B$15)

 

[cedricthecat]

Brilliant! Thanks for that konew! Looks like a great way to go