SUMPRODUCT with Wildcards

[Sachin]

I'm working on a revision of a report my team runs every week. I've decided to take the plunge into SUMPRODUCT after reading about it here.


I have many different columns to look up, but basically I'm worried about two: The date of a particular action and the organization that the action belongs to. In terms of the organization, there is a two step heirarchy, call them Division and Office.


So you can have the following Division - Office combinations:


Chandoo - Excel

Chandoo - Word

Hui - PowerPoint

Hui - Outlook

SirJB7 - Access

SirJB7 - OneNote


The Division - Office combo is in a column called Lookup DivOfc, which is in a datatable called DataTable. The date range I'm searching between is PrevReportDate and ReportDate. The specific combo I'm querying against is in cell E6. Cell B6 has just the Division by itself.


I've been able to build a SUMPRODUCT to look at dates and a particular Division Office Combo:


=SUMPRODUCT((DataTable[[#Data],[Lookup DivOfc]]=E6)*(RecruitData[[#Data],[Date]]>=PrevReportDate)*(RecruitData[[#Data],[Date]]<=ReportDate)))


So here's my issue:


I need to be able to look at three levels of the heirarchy. In effect, I want to able to report for "Chandoo - Excel", "Chandoo - All" (Both Chandoo - Excel and Chandoo - Word), or even All.


So this is the formula I've developed:


=SUMPRODUCT((Data[[#Data],[Lookup DivOfc]]=IF(E6="(All)",IF(B6="(All)","*",B6&"*"),E6))*(DataTable[[#Data],[Date]]>=PrevReportDate)*(DataTable[[#Data],[Date]]<=ReportDate))


But this formula returns the #VALUE error.


Any thoughts on how I can get it to return a value?


Thanks,


Sachin

 

[Hui]

Sachin


Suproduct doesn't work well with wild cards, but as always there are otehr ways to work around it


To extract all records that match say Chandoo* use:

=SUMPRODUCT((DataTable[[#Data],[Date]]>=DateFrom)*(DataTable[[#Data],[Date]]<DateTo)*(LEFT(Data[[#Data],[Lookup DivOfc]],LEN(B6))= B6))


To extract all records that match say "Chandoo - Excel" use:

[code]=SUMPRODUCT((DataTable[[#Data],[Date]]>=DateFrom)*(DataTable[[#Data],[Date]]<DateTo)*(LEFT(Data[[#Data],[Lookup DivOfc]],LEN(B6)+len(" - Excel"))= B6 & " - Excel"))

To extract all records that match just the date use:

=SUMPRODUCT((DataTable[[#Data],[Date]]>=DateFrom)*(DataTable[[#Data],[Date]]<DateTo))[/code]

 

[Sachin]

Hui, thank you!


I was afraid that SUMPRODUCT wouldn't handle the wildcards very well. Oh well, back to the multiple COUNTIFS.