Excel Formula Help: Number Matching

[bawrites]

I'm having difficulty getting my head around how to fix this and am hoping someone will have a solution to my dilemma.

I have a column on my source data sheet that contains data like "Reg 1, Reg 2, Reg 3, Reg 4.. Reg13, Reg14, Reg03, Reg04, etc..."

I'm attempting to break out the number of occurrences for each Reg number and summarize to a table similar to what you see below.

[TABLE="width: 192"]
<colgroup><col span="3"></colgroup><tbody>[TR]
[TD]Store #[/TD]
[TD]Reg #[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]4[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]6[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

The issue I'm running into is that I used a wildcard to match the type in the register source data column, and its matching not only the "3" for 3, it's also matching the "3" and counting it in "13" and "23".

=COUNTIFS(Data!N:N,$W$5,Data!D:D,"*3*",Data!M:M,">"&$T$4)

Data!N:N is a column containing the store number, and $W$5 is a manually entered store number field.
Data!D:D is the register source data column, with, in this case, *3* being my wildcard.
Data!M:M is a date column, and the cell $T$4 is a date field.

Is there an easy way for me to match *only* cells that contain 3 or 03 but not 13, and 23 - for instance?

The real headache of this is that the source data is inconsistent.

 

[Ken Cowen]

Your inconsistent data really complicates things. I would recommend that you make a consistent data column, which it appears you can do by simply substituting a 0 for all the spaces in your data, converting Reg 1 to Reg 01, and allowing you to sum the 01, 02, etc. But if your numbers get bigger than 99 or if you have other places where you have spaces, eg. trailing spaces, that won't work.

If the only text in your data column is, as in your sample data, Reg, you could strip out the Reg and any spaces, then do your counts on numbers.

Good luck

Ken

 

[63falcondude]

You can try

=SUM(COUNTIFS(Data!N:N,$W$5,Data!D:D,{"* 3","*03"},Data!M:M,">"&$T$4))

Changes to your formula marked in red.

This will count anything ending in 3 or 03 (while matching the other criteria as well).

 

[bawrites]

Worked like a charm - and then I found out just how much inconsistent data are.

You gave me a new trick though (the {array of conditions}) - and that helped immensely.

Thanks!!

You can try

=SUM(COUNTIFS(Data!N:N,$W$5,Data!D:D,{"* 3","*03"},Data!M:M,">"&$T$4))

Changes to your formula marked in red.

This will count anything ending in 3 or 03 (while matching the other criteria as well).

 

[63falcondude]

You're welcome. Always happy to help.