Awhile ago there was a thread (thread68-720840) going whereas xlbo wrote the following formula:
=SUMPRODUCT((Left($B$1:$B$4,3)="SER")*($C$1:$C$4="Policy"))
I'm trying to modify this formula to work for a project that will be done on a daily basis.
Here's the scenario:
Column A will consist of several rows of account numbers. The numbers are 16 digits long but the first 4 digits distinguishes which client they belong to. I want to be able to get a count of how many accounts belong to CompA, how many accounts belong to CompB, how many accounts belong to CompC, etc.
SO...what I did was use the following formula:
=SUMPRODUCT((Left($A$1:$A$1000,4)="5463")
I also need to let you know that, because the account numbers are 16 digits, I had to change the cell format to text otherwise I get a scientific notation instead of the complete 16 digits (i.e., 5.46312E+16).
5463222263239852
1233222263239852
5463233423232342
5463233263239903
5463229863239834
8785243864582847
Using the above as an example, I SHOULD come up with 4 as my total for how many accounts start with 5463. Unforunately, I keep coming up with a 0 (zero) as my answer when I know there are accounts out there starting with 5463.
What AM I doing wrong?
=SUMPRODUCT((Left($B$1:$B$4,3)="SER")*($C$1:$C$4="Policy"))
I'm trying to modify this formula to work for a project that will be done on a daily basis.
Here's the scenario:
Column A will consist of several rows of account numbers. The numbers are 16 digits long but the first 4 digits distinguishes which client they belong to. I want to be able to get a count of how many accounts belong to CompA, how many accounts belong to CompB, how many accounts belong to CompC, etc.
SO...what I did was use the following formula:
=SUMPRODUCT((Left($A$1:$A$1000,4)="5463")
I also need to let you know that, because the account numbers are 16 digits, I had to change the cell format to text otherwise I get a scientific notation instead of the complete 16 digits (i.e., 5.46312E+16).
5463222263239852
1233222263239852
5463233423232342
5463233263239903
5463229863239834
8785243864582847
Using the above as an example, I SHOULD come up with 4 as my total for how many accounts start with 5463. Unforunately, I keep coming up with a 0 (zero) as my answer when I know there are accounts out there starting with 5463.
What AM I doing wrong?