• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to sum columns based on two row references

R

runway

New Member
Hi,


I am trying to make my and my co-workers lives easier. We receive reports twice a day that we manually wrangle for necessary data. I would like to be able to pull the necessary data using formulas so we can just copy/paste the results (as values) into our report presentation.


Attached is a sample file.


As you will see the first column can contain up to 7 different vakues all in merged cells. The second column can contain up to 25 different values, the next few columns contain various numbers that we need to total.


For instance I would need the total of all values in column D for the value of apples in column A. Then I would need the total of values in column D if A was apples and B was orange, lemon and berry2.


I hope this makes sense. If anyone can help with those two examples I can play with the formulas to get everything else I need probably. The idea would be to have a row of cells towards the bottom of a worksheet and twice a day copy/paste a report onto the worksheet and then copy/paste the values that the formulas generate in their cells to the main report.


I have been playing with vlookup but getting nowhere as I need multiple values totalled based on multiple references.....


Any help or pointers greatly appreciated.


http://www.mediafire.com/?0depolxhh9d5dag
 
Hi runway,


Not sure I entirely understand the whole query, but here's my starter for 10 for the first part.


Select all merged cells in column A and deselect Merge & Centre.


With the cells still selected, go to Find & Select>Go to Special and select Blanks>OK, then type = and press up arrow once, then Ctrl-Enter.


=SUMPRODUCT(--(A4:A24="apple"),D4:D24)


Hope this helps for starters?
 
Ok, I see what you did there. It's a useful start. Needs some preparation of the report to get the result but if the other required results can be got without any extra prep, just formulas, it's a good start.
 
Hi,


Try this one


=SUMPRODUCT(--(A4:A24="apple"),--(B4:B24="orange"),(D4:D24))+SUMPRODUCT(--(A4:A24="apple"),--(B4:B24="lemon"),(D4:D24))+SUMPRODUCT(--(A4:A24="apple"),--(B4:B24="berry"),(D4:D24))
 
I tried sumproduct earlier today and got value errors but it's working beautifully now.

The unmerging and filling the cells in A with the correct values has made everything come together.


Thanks a bundle, I will get this all up and running over the next few evenings at work and roll it out to my colleagues by the end of the week. Looks like Friday cakes in the office will be coming out of other peoples pockets this week!

Thanks!
 
You must log in or register to reply here.
Back
Top