Lookup and return cell value

[jenwantz]

I am currently trying to create a report that, from the attached data, summarized the amount of revenue by state by customer. I am assuming I will need a lookup of some kind. So I think I am looking for a formula that looks at a portion of the spreadsheet and finds the abbreviation for a state, then returns that revenue amount to the left of it. I need help. I am open to any suggestions.

Thank You in advance

[Winon]

Hello jenwantz,

I would suggest you create a Table Report, and then use DSUM to get the desired results.

Anyone else with a better idea?

[jenwantz]

thank you for your help

[jenwantz]

I am not very familiar with a pivot table. I have attempted to insert it. I am not sure how to incorporate the dsum. Do you have any more direct instructions?

[Winon]

Hi jenwantz,

If you could upload a sample WorkBook, showing what you need done how, then we may be in a better position to assist.

[jenwantz]

There is a sample attached

[OnErrorGoto0]

That's not a great format to work with, but you need something like this.

[Winon]

@0EGO,

Great Job, my friend!

Once again, you have demonstrated to not only look at the given, but rather the solution. And that initially lies within the build of the WorkBook, which you have so nicely fixed.

I myself, would not have been able to do it better.

Thank you for helping once again!

@ jenwantz,

So the solution turned out to be SumProduct and not Dsum, and some restructuring !...

Come on now, you have to agree. OnErrorGoto0 deserves a good Rep for his help!

[jenwantz]

Thank You so much. I really appreciate it. This is a life saver and exactly what I was looking for. I can not express my full gratitude. You made my week.

[OnErrorGoto0]

My pleasure. Please don't forget to mark the thread solved.

[jenwantz]

One more quick question. What if it needs sorted by another criteria? I am trying to sort sales by company by state by product line. This sort is by sales by state by company currently?

[OnErrorGoto0]

It's not a sort, but you would need to add an additional range and criterion to the SUMPRODUCT formula of the form
*(range=criterion)
in the first part of the formula.

[jenwantz]

Thank you so much for all of your help and quick responses