Using COUNTIFS with the LEFT function

[gumbles]

Hello,


I am trying to count the number of entries in 3 tables that have "M5" as the first two digits by combining a countifs with the left function but I cannot make it work.


My attemps (which only use one table as they are tests) to far are as follows:


=COUNTIF(Initiated_Document_Request[Doc. Reference],LEFT(Initiated_Document_Request[Doc. Reference],2)="M5")


=COUNTIF(B:B,LEFT(Initiated_Document_Request[Doc. Reference],2)="M5")


But none return what Im after. As you may have realised the column im measuring is in column B.


Any Ideas?


Regards,


Gumbles

 

[Hui]

Gumbles


I'd use: =SUMPRODUCT(--(LEFT(Initiated_Document_Request[Doc. Reference],2)="m5"))

 

[gumbles]

Thanks Hui that works Great.


Do you have any advice for including 2 other table ranges in this without alot of repeating?

 

[mgesh2002]

Gumbles,

I think, now you can use the SUM function to count, may be like this:


=SUM(SUMPRODUCT(--(LEFT(Table#1,2)="m5")),SUMPRODUCT(--(LEFT(Table#2,2)="m5")),SUMPRODUCT(--(LEFT(Table#3,2)="m5")))


Hope I am right! Please check.

 

[gumbles]

Hey Murugesh,


Yea I had this idea too, but was seeing if there might be a more elegant solution but I guess not ha.


Thankyou for you input though, it has confirmed my initial idea.

 

[Hui]

You don't need the Sum() function


=SUMPRODUCT(--(LEFT(Table#1,2)="m5"))+SUMPRODUCT(--(LEFT(Table#2,2)="m5"))+SUMPRODUCT(--(LEFT(Table#3,2)="m5"))

 

[gumbles]

Cheers Hui.