COUNTIFS - Criteria: Begins with "PROJ" and ends in a digit 1

[RP1America]

=COUNTIFS($E:$E,"PROJ*",$B:$B,">="&$M$3,$B:$B,"<="&$N$3)

For column E, I would like to count only if what begins with "PROJ" also ends with in a digit (i.e. PROJ1234).

Any thoughts?

Thanks!!

 

[SkipVought]

Help us (and yourself) out!

Just give a hint of what you have in column B and the values in M & N.

But, as one surgeon said to the other, "Suture self!"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I'd use SUMPRODUCT.

Caveat: Cannot use COLUMN references
[tt]
=SUMPRODUCT(--(LEFT(E1:E999999,4)="PROJ"))
[/tt]
sans the 'undefined' as defined above.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

missed the digit requirement...
[tt]
=SUMPRODUCT(--(LEFT(E1:E999999,4)="PROJ")*(RIGHT(E1:E999999,1)>="0")*(RIGHT(E1:E999999,1)<="9"))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gammachaser]

Maybe I am missing something here, but why can't you use

Like "PROJ*#"

Meets all the requirements -- Starts with 'PROJ', may or may not have some other characters and end with a digit.

 

[SkipVought]

So how would you use like "PROJ*#" in a formula on the sheet? Remember this is forum68, not forum707.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gammachaser]

Actually, "PROJ*" (without the LIKE part) works... unfortunately Excel apparently does not recognize '#' (or any other character) as a numerical wildcard, so that is the failure. Close, but no cigar.

 

[RP1America]

Thanks for the info, all!

And sorry for not defining my information, Skip.

Column B holds dates.
M3 holds a low date (i.e. 6/1/11)
N3 holds a high date (i.e. 6/24/11)

This:

=SUMPRODUCT(--(LEFT(E1:E999999,4)="PROJ")*(RIGHT(E1:E999999,1)>="0")*(RIGHT(E1:E999999,1)<="9"))

...worked great for column E. Yet I still need to only count if it falls within the date range of M3-N3. How would I accomplish that?

Also, for an additional reporting item, I need to basically do the same thing, but with a SUMIFS. Where all of the same criteria must be met, yet we would be summing the Numbers in column C.

 

[SkipVought]

Using the same principle...
[tt]
=SUMPRODUCT(--(LEFT(E1:E999999,4)="PROJ")*(RIGHT(E1:E999999,1)>="0")*(RIGHT(E1:E999999,1)<="9")*(E1:E999999>=M3)*(E1:E999999<=N3))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

oops, change the range references to B from E for the Date criteria

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

Thanks Skip, that did it!

So what about needing this same criteria for an additional reporting item? Instead of counting, I would need to SUM column C.

Currently I have:

=SUMIFS('[Time Tracking.xlsm]Tracking'!$C:$C,'[Time Tracking.xlsm]Tracking'!$E:$E,"PROJ*",'[Time Tracking.xlsm]Tracking'!$B:$B,">="&$M$3,'[Time Tracking.xlsm]Tracking'!$B:$B,"<="&$N$3)

But need to add in the additional criteria of PROJ ending in a digit.

 

[SkipVought]

[tt]
=SUMPRODUCT((C1:C999999)*(LEFT(E1:E999999,4)="PROJ")*(RIGHT(E1:E999999,1)>="0")*(RIGHT(E1:E999999,1)<="9")*(E1:E999999>=M3)*(E1:E999999<=N3))
[/tt]
BTW, the relative position of the sum range and criteria is irrelevant.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

Thanks again, Skip! I really appreciate the help!

The SUMPRODUCT formula has seemed to really slow down the calculation process. Is this normal? If so, I am imagining that it has something to do with going through all 999999 rows of data each time.

 

[SkipVought]

I'd modify the range to the rows of data you want to analyse, wouldn't you?

Also set calculation to MANUAL and force calculation on demand.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

This may be way off, but is there a way to get around this by defining that you want the calculation to end with the last row with data in it?

Say, M1:M(LastRow), or something to that affect?

 

[SkipVought]

Every table & list must have unique headings.

Insert > table -- my table has headings

Use the Table references, for instance my table has 2 columns
[tt]
Dte Name
[/tt]
The Table name is Table1

So here is a typican range reference...
[tt]
=INDEX(Table1[Dte],Match(zz1,Table1[Name],0),1)
[/tt]
The table reference automatically adjust as you add or remove rows of data.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}