DSUM multiple criteria single headings 2

[skitrumpet]

Hi,

Apologies if this has been asked before, I wasn't able to track anything down with a search.

Is it possible to use the DSUM formula with one standard set of headings and multiple criteria below them and return data based on each individual row.

For example:

Product Country
Teddy bear UK Return total teddy bears in UK
Colouring book US Return total colouring books in US
Teddy bear France Return total teddy bears in France
Drum US Return total drums in US
Colouring book UK Return total colouring books in UK

I think if I use DSUM for the second combination, colouring book and US, and select the range to include the category headings, I will also get the number of teddy bears in the UK.


Thanks in advance for any help

Adam

 

[SkipVought]

Hi,

Why not use a PivotTable?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gavona]

No it isn't possible. That is how you specify an OR condition to DSUM.

If a pivotTable won't do it for you (why wouldn't it?) then I would use SumProduct. Using named ranges something like:
=sumproduct(--(Product=A2),--(Country=B2))
or
=sumproduct(--(Product=A2),--(Country=B2),Quantity)

You could also have DSUM with hidden rows.


Gavin

 

[skitrumpet]

Thanks for your comments guys.
I guess I was trying to be too clever and completely ignored the pivot table.

I'll keep it simple next time!


Cheers

Adam