Using Dynamic Criteria with DCOUNT/DSUM
 

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.

Using Dynamic Criteria with DCOUNT/DSUM
 

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/f...es/pivot1.html

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


--
Regards

Roger Govier


"Nigel Rablin" wrote in message
...

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.




--
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..

Using Dynamic Criteria with DCOUNT/DSUM
 

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
--
Regards

Roger Govier


"Nigel Rablin" wrote in message
...

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;498674 Wrote:
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/f...es/pivot1.html

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


--
Regards

Roger Govier


"Nigel Rablin" wrote in message
...-

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.




--
Nigel Rablin -




--
Nigel Rablin