Wildcards in Concatenate? Help putting it together?

[Dcbus.1]

I’m creating a benchmarking spreadsheet that identifies outliers compared the last year’s average units per date/transaction.

A Date can have the same Transaction Code several times, so I need the net Units per Date and Transaction Code.

I tried concatenating cells for Store Name, Transaction#, Product Code, and Date into one cell that appear like this: "Store Name^Transaction#^Product Code^Date" and working it from there. I hit a wall on how to create this.

I tried, but don’t know if wildcards work in these situations.

=COUNTIF($S$6:$S$5000,">="&$O6&"^" WILDCARD? "^"&$P6&"^"&6-365)-COUNTIF($S$6:$S$5000,">="&$O6&"^"WILDCARD? "^"&$P6&"^"&$H6)

What’s the best way to accomplish this with Excel 2003?

Also, what is the best way to include the Units per Transaction and Date with an absolute value greater than 0?

Attached is a sample data set.sample.xls

[JBeaucaire]

Your formula above doesn't match your sample sheet. Care to offer an example from the sample of what/where you enter values, where you're trying to put this new formula, and what the expected answer would be based on these answers?

Post an updated sample workbook that fully demonstrates the goal, too.

[Dcbus.1]

Here's a working copy of the sample spreadsheet with formulas. sample2.xls

I added a lot to it since my earlier post.

I think it works (without wildcards), with 1 exception. I need to figure out how to "GroupBy Sum" like in Access to get the total Units per Date per Transaction Code. It's counting them separately now, but in reality the units should be Summed per Date/Transaction. Can this be done in Excel? I can always export out to Access, GroupBy Sum there, then bring it back to Excel???

[JBeaucaire]

As I read your answers with your current method, don't agree with them. For instance, the # of units in the past year for:

Store: 44.95
Chrg Code: 647550

...should be the sum of rows 4 & 5, a total of 4. Your formula in G4 results in 0, not 4.

The Concatenate thing is cool, and there are many instances where I would use that. But I would defer to a standard SUMPRODUCT() formula for this, with no need at all for the helper column.

In G2 and copied down:

=SUMPRODUCT(--($D$4:$D$51=$D4), --($E$4:$E$51=$E4), --($A$4:$A$51>$A4-365), --($A$4:$A$51<=$A4), $B$4:$B$51)