Re: Excel CountIf() or SumIf() with multi criteria 1

[AlsGal52]

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?

 

[Gavona]

various ways but you have to make the result numeric
=SUMPRODUCT((LEFT($A$1:$A$1000,4)="5463")*1)
or
=SUMPRODUCT(--(LEFT($A$1:$A$1000,4)="5463"))


Gavin

 

[AlsGal52]

Gavin, I could just HUG you and give you THOUSANDS of gold stars!!!

If you only KNEW how much time you've saved me and others.

Thank you EVER so much.

 

[byundt]

You could also use COUNTIF with a wildcard:
=COUNTIF(A$1:A$1000,"5463*")

Or if the 5463 is in cell D1, you could use:
=COUNTIF(A$1:A$1000,D1 & "*")

Brad

 

[KenWright]

You might also want to consider using a Pivot table or an autofilter on that data as well. You could easily create a helper column with a formula such as =LEFT(A2,4) that gave you the first 4 characters of the 'number', and then use that as a field to filter on, or group data with in the Pivot table.

That would give you a bit more analystical power for playing with large lists of data.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[AlsGal52]

thanks byunt & KenWright, but Gavona's answer was exactly what I needed. Your ideas were also good ones, but I have beginner level Excel people who will be using the template that I created so I needed to KISS if you know what I mean.

Every month they have to download reports and then get a subtotal of how many accounts belong to each client and then a grand total. Unbeknownst to me, they've been doing this manually for over a year until someone wizened up and suggested asking me for a better way to get their answers (and yes, I did tell them where I got the correct formula to use - both from my husband (a programmer) and Tek-Tips - Gavona).

The problem with your ideas is that there's a small hitch. There are SOME account numbers which are shorter numbers but their first 4 digits will never be the same as the ones belonging to actual clients and THEY belong to a specific client as well.

So, taking Gavona's idea and then getting the subtotal for the shorter numbers, they now have a breakdown of each of the clients and a grand total. They don't have to pull down anything and once they gather the external data, the workbook does all the rest of the work for them.

But, I do thank you both for your contribution. ;-)

 

[Gavona]

Firstly, thanks for all the hugs and kisses (not to mention gold stars)!
Although I answered your specific question I do feel that the others have a point. Certainly I would tend to go for a helper column containing Client.
Consider:
What will you do if the total for all the clients you have specified does not equal the total in your 'database'? How will you find the new client? A pivot table has the huge benefit of reporting everything. (Alternately you could put your client references in cells beside your formulae as Byundit suggested. Populate these with advanced filter to a new location, unique.)
Refreshing your summary could be assigned to a macro, maybe with a 'refresh' button on the workbook. Even if all it does is refresh the pivot table. Or you could encourage the users to right click and refresh the pivot - thus helping them to develop their confidence in Excel.



Gavin