Please help! Please help!

[G]

Earlier I posted a question, please if someone can tell me if this is doable or not.....

I have a sheet "DATA" with 100's of rows of information, some of the data in column A is duplicated as it represents a specific store. I have created a form that when you enter the store # the remaining data will automatically populate. However I am stuck in 1 area. There is no way for me to know exact cell locations for my data, so I need to base my formula on a range. If there is someone who can assist me, I will send a condinced example of what I am working on.

Please! Please! Please........

 

[G]

The formula in J9 is what I want. The information in that cell is correct, but J10 thru J13 is incorrect as I need excel to return the value below that located in column D. IE... I need to match the category Computers, ect. to the category at left and return the value to the right. My original sheet has over 700 rows of data. There for I need to do this as a range with the amount of rows that I am working with.

 

[Andrew Poulsom]

Enter in cell J9:

=SUMPRODUCT((A$2:A$14=$G$6)*(C$2:C$14=I9),D$2:D$14)

and copy down.

Amend the last row in the ranges as necessary.

 

[G]

Andrew,

That is what I needed. My only question the D$2:D$14 on the end, how come I can not enter D:D and select the entire range? Is it because of the TEXT in D1?

Thank you for your help..... You do not know how much I have been looking forward to an answer.

Thanks,
Gabbrielle

 

[Andrew Poulsom]

For SUMPRODUCT to work the arrays must be the same size so you'd have to change them all. I don't think it accepts entire columns, but you can try.

 

[G]

Yes that is what I have been wanting, have I had tunnel vision?


Sincerely,
G