DSUM Help.

[samshiells]

Hi,

I am building a interactive spreadsheet where the user can select from 5 filters (e.g. department, team, name, etc) on certain items which will then update the table being viewed.

I'm unable to use Pivot tables as it has to work between 2003/2007. I've got it working using Sumproduct but is working too slowly.

So finally have ended up trying to use the formula below.

=SUM(IF(
(Reason=$B6)*
(Month_Received=D$5)*
(Arrears_RelatedComplaint="Arrears Related")*
(Department=B2),Complaint_Count),0)

The problem I have is that where Department=B2 this is one of the filters. When a department is selected that's fine but when it is blank it isn't pulling through any data as obviously it's trying to match where department = 0.

Is there anything I can put in this field where it will return all fields?

If you need anything explaining further or have any other suggestions how to speed up my spreadsheet (without using Access) please let me know! I have roughly 30 columns and 30,000 rows of data which it's querying.

Thanks, Sam

 

[Robert Mika]

I can not see your data but try like this:
=SUM(IF((Reason=$B6)*(Month_Received=D$5)*(Arrears_RelatedComplaint="Arrears Related")*(Department=B2)*(Department<>0),Complaint_Count),0)

 

[Aladin Akyurek]

Hi,

I am building a interactive spreadsheet where the user can select from 5 filters (e.g. department, team, name, etc) on certain items which will then update the table being viewed.

I'm unable to use Pivot tables as it has to work between 2003/2007. I've got it working using Sumproduct but is working too slowly.

So finally have ended up trying to use the formula below.

=SUM(IF(
(Reason=$B6)*
(Month_Received=D$5)*
(Arrears_RelatedComplaint="Arrears Related")*
(Department=B2),Complaint_Count),0)

The problem I have is that where Department=B2 this is one of the filters. When a department is selected that's fine but when it is blank it isn't pulling through any data as obviously it's trying to match where department = 0.

Is there anything I can put in this field where it will return all fields?

If you need anything explaining further or have any other suggestions how to speed up my spreadsheet (without using Access) please let me know! I have roughly 30 columns and 30,000 rows of data which it's querying.

Thanks, Sam

Control+shift+enter, not just enter:

=SUM(
  IF(Reason=$B6,
  IF(Month_Received=D$5,
  IF(Arrears_RelatedComplaint="Arrears Related",
  IF(Department=IF(B2="",Department,B2),
   Complaint_Count)))))

For DSUM, see:

http://www.mrexcel.com/forum/showthread.php?t=58539

plus the MS sources the link quotes.

Another option for more speed is to create a concatenated range: That is, a range that concatenates the values from Reason, Moth_Received, Arrears_RelatedComplaint, and Department. Example:

=A2&"|"&B2&"|"&C2&...

Such would allow in your wb:

=SUMIF(ConcatenatedRange,$B6&"|"&D$5&"Arrears Related&"|"&IF(B2="","*",B2),ComplaintCount)