Using Index, Match and summing the Multiple matches

test for excel forum.zip

Please Help!
I am trying to Sum the Total usage -BY LOCATION - From Sheet 1 (copier Meter Log), to Sheet 2 (Usage Reference Tab).
For Example, Sheet 2 D11 Should be the sum of all B&W Total Usage on Devices for Canada Only. Sheet 2 D12 Should be for Canton etc.

I have created Column D on Sheet 1 with a header "B&W Location". Each time a location is seen, as you scroll down the column, I need excel to go over "X" number of columns and return the summed value for that month, at that particular location, every time it comes across tha location in column D.

Note: I would like to select the "Month Number" (Sheet 2 D6), and have the totals for that month displayed.

Any Help would be greatly appreciated! Please let me know if I need to clarify further...

Another note. I am not comfortable with VB or Macros, I am just looking for a possible formula...

I think it's too hard to provide help without seeing what you want - can you post a sample worksheet?

From what you are describing, SUMPRODUCT() or SUMIFS() should do what you want!

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.
When you reply please make it clear WHO you are responding to by mentioning their name.
If we have been of assistance, please let us know. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Hi David, thanks for the reply. I did attach a worksheet, it's called "test for excel forum.zip". Perhaps I didn't attach correctly.. Please let me know if I need to start a new thread and attempt to re-attach and not zip the file. Attached again, is the zip file.. I appreciate your help...

Ford is correct, I missed your attachment. (Struggling with a really hot day today - 40 degrees! - that's about 104 for you guys).

=SUMPRODUCT(--('Copier Meter Log'!$D$5:$D$619='Usage Reference Tab'!B11)*'Copier Meter Log'!$J$5:$J$619) will give you the total you want. BUT I can't work out how to get it to change based on the month.

Hopefully one of the more talented members can help.

Originally Posted by David A Coop

Ford is correct, I missed your attachment. (Struggling with a really hot day today - 40 degrees! - that's about 104 for you guys).

=SUMPRODUCT(--('Copier Meter Log'!$D$5:$D$619='Usage Reference Tab'!B11)*'Copier Meter Log'!$J$5:$J$619) will give you the total you want. BUT I can't work out how to get it to change based on the month.

Hopefully one of the more talented members can help.

Hello again Shameus,

I've seen this model before!!! I can see my footprints in the A7, A24 etc.

Anyway, I managed to work it out!

=SUMPRODUCT(--('Copier Meter Log'!$D$5:$D$619='Usage Reference Tab'!B12)*INDIRECT(ADDRESS(5,$D$6,1,1,"Copier Meter Log")&":"&ADDRESS(619,$D$6,1,1)))

This formula in cells D11:D20 should do the totaling you need, and be flexible based on the "Month Number".

It has one disadvantage you need to be wary of, the ADDRESS() part of the function has got row numbers keyed in (5 and 619) which won't change automatically. Not sure how to make these "dynamic", but if you change the dimensions of the range, you will have to manually change these as well or the formula will crash.

I note that the formula in E6 is giving a #REF error. This is because you need to change the range for Months to include row 6. Can't help with the rest!

Hope this helps,

Regards,
David



When you reply please make it clear WHO you are responding to by mentioning their name.
If we have been of assistance, please let us know. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

Ha Ha!! Affirmative on the "footprints" you see! I'm going to have to figure out how to change the Author of this file to David A Coop!

You have some amazing talent. I appreciate your "wizardry" as well as your time. I'm having trouble just "decoding" your formula!

There is one issue though. I'm excited to see that a number is being returned, unfortunately it's the wrong number. IT SHOWS "1000" for month 10 (Dec 2013), when it should show 800. And when I switch it to month 11, it shows a "0". It should show "321".

I'm hoping once we get this formula right, it will be easy for me to copy/paste into the "color" portion of sheet 2 (F11).

Do you mind taking one more look at it?

Again, thank you for your time, your help and your "wizardry"... I already owe you BIG!

BTW, try your best to stay warm. We are getting pounded by unseasonably warm storms here in Tennessee tonight...

OOPS! I copied the formula from row 12 instead of row 11, but thanks for your kind encouragement.

=SUMPRODUCT(--('Copier Meter Log'!$D$5:$D$619='Usage Reference Tab'!B11)*INDIRECT(ADDRESS(5,$D$6,1,1,"Copier Meter Log")&":"&ADDRESS(619,$D$6,1,1)))

I'll leave you to figure out the formula for now, but if you like I'll explain it after Christmas - I am time poor at the moment! If you want some explanation, let me know.

It won't be any problem staying warm over Christmas - we are expecting 30 degrees. That's 86 deg in your money!

David

File with slight changes in D6, C7 & D7.
D6 validated to select numbers 1 to 12 ( Month Numbers),C7=Year & D7=2013 Required Year.

If you don't want any change in your original file use in J11

Please Login or Register  to view this content.

Perfect! Thanks so much- works great! Sorry for the late reply- been a long holiday break...

Originally Posted by FDibbins

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

I believe I have successfully "solved" the thread and have utilized the "reputation" function. Thanks for the info!