Using Dynamic Criteria with DCOUNT/DSUM

[Nigel Rablin]

I'm trying to DCOUNT with multiple criteria - but with one of the
criteria changing.

I have a list with ..

Date - ID - Name - Data - Type


Where there are many "transactions" for each ID over a month.

I advance filter the ID's over to a new sheet - and I'm trying to get a
new list

like...

ID - Combined Data

but only for a certain 'Type'

Basically I can do it with DSUM with multiple criteria...

ID - Type

but I have to change for every ID - or have a criteria listed for every
ID which is more than annoying seeing the ID list changes depending on
the month of Data I pull into it.

Sorry if this is confusing - I am probably thinking about this in the
wrong way - and there is probably a simpler way of doing it.

 

[Roger Govier]

Hi

Try using a Pivot Table,
For more help with Pivot tables take a look at the following sites
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html

http://www.datapigtechnologies.com/flashfiles/pivot1.html

http://www.edferrero.com/Tutorials.aspx

 

[Nigel Rablin]

Thanks but that's exactly what I am trying to avoid...

I hate pivot tables - easy to use, but they take up so much resources
and they are not really flexible enough..

 

[Roger Govier]

Hi Nigel

Can't agree with your comments about PT's, as IMHO they are one of the
best features of Excel.
Still, each to his own view.
Without bothering to extract data to another sheet with Advanced Filter,
set up a grid on another sheet as follows
Type1 Type2 Type3
ID1
ID2
ID3
then in cell B2 of this new sheet

=SUMPROUCT((Sheet1!$B$2:$B$1000=$A2)*(Sheet1!$E$2:$E$1000=B$1))
copy across and down