Simple Dsum Question

[Matt Akers]

I have a spreadsheet table with, say, a column for the salesperson, a column for products and a column with sales.

I want to know - using dsum - total sales where the sales person is ("Smith" or "Jones") and product is ("apples" or "oranges")

I assumed my criteria table would look like this:

Salesperson....Product
Smith...............Apples
Jones...............Oranges

But I find its only giving me sales where the salesperson is Smith and how many apples they sold, or the person is Jones and how many oranges they sold.

How do I set up my criteria range so shows sales for both apples and oranges for Smith and Jones combined?

I'm hoping you're not going to say I have to list each person twice with both apples and oranges against their name (ie 4 lines in total) - my actual spreadsheet contains far more possible combinations.

with thanks.

[SDCh]

Hi, Matt

Why you not try pivot table?

[oeldere]

With an pivot table.

See the attached file.

[Matt Akers]

Thanks for your help guys.

However I was avoiding pivot tables for 2 reasons:

1) The file has very dynamic data and is to be used by Excel novices (not necessary a deal breaker for pivot tables...)

2) Because of the way the incoming data is formatted (I paste a range with titles into a template), the title of the column(s) to sum changes regularly (ie its not just "sales", it could be "orange sales" in one report, "apple sales" in the next, BOTH in the next one etc...) this would require someone to reconfigure the pivot table each time wouldn't it?

This is why I was hoping to do it with Dsum (dsum would just use what ever column title it points at)

Any suggestions?

Thanks again

[oeldere]

1) I don't find that a reason => learn users to work with pivot table.

2) I suggest you have to change the criteria with the Dsum also (or am I wrong?)

I'm not familiar with Dsum.

so what is the differance with pivot table?

[Kevin UK]

Hi Matt Akers

Perhaps the Sumproduct formula.
Column A = Names
Column B = Fruit
Column C = Sales

Please Login or Register  to view this content.

[SDCh]

See the attachment, may be this what you looking for