Counting if...

[Thomas]

Hi all,

I have a large spreadsheet where I'm trying to use the COUNTIF command, but
I'm not sure how to use it. I am trying to count actualized dates (column B),
based on a geographical ID (column A)

Column A has a ID for a project. The first two letters identify the
geographical location of the project, while the remaining characters are
unique per project.

Column B, has a date if the milestone has been actualized.

What I'd like to do is be able to count the number of dates in column B
based on each geographical location.

For example:

Column A:

BD1
BD2
KF1
KF2
WA1
WL1


Column B
1/7/09
1/15/09

2/10/09
1/14/09

1/16/09

To add to this, one geographical area has more than one "geo-code", and I
tried to show that here with WA and WL. Both of these cover the same area.
So in this case I'd see:
BD: 2
KF: 1
WA & WL: 1

Can anyone help with this?

Thanks!
Thomas

 

[Bob Phillips]

=SUMPRODUCT(-(LEFT(A2:A20,2)="GB"),--(B2:B20<>""))

 

[Thomas]

Thanks Bob! That works!

Is there a way to count the ones that start with WA or WL under one?
Something like:

=SUMPRODUCT(-(LEFT(A2:A20,2)="WA" or "WL"),--(B2:B20<>""))

This one doesn't work, but is there something similar I could use? Most of
my projects have just one geographical code, but then there's one market with
4 different codes, so I need to be able to count and combine those.

Thanks!
Tom

 

[Thomas]

Thanks Bob! That works!

Is there a way to count the ones that start with WA or WL under one?
Something like:

=SUMPRODUCT(-(LEFT(A2:A20,2)="WA" or "WL"),--(B2:B20<>""))

This one doesn't work, but is there something similar I could use? Most of
my projects have just one geographical code, but then there's one market with
4 different codes, so I need to be able to count and combine those.

Thanks!
Tom

 

[Pete_UK]

I'm not Bob, but you could try this:

=SUMPRODUCT(((LEFT(A2:A20,2)="WA")+(LEFT(A2:A20,2)="WL"))*
(B2:B20<>""))

The + acts as an OR, and the * acts as AND.

Hope this helps.

Pete

 

[Thomas]

Hi Pete!
That did it! Thanks for your help!!

I'm not Bob, but you could try this:

=SUMPRODUCT(((LEFT(A2:A20,2)="WA")+(LEFT(A2:A20,2)="WL"))*
(B2:B20<>""))

The + acts as an OR, and the * acts as AND.

Hope this helps.

Pete

 

[Thomas]

I tried to respond to this once already, but I guess it didn't work.

Thanks Pete! That took care of it!

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

 

[Bob Phillips]

=SUMPRODUCT((LEFT(A2:A20,2)={"WA","WL"})*(B2:B20<>""))

 

[Pete_UK]

Yes, it did get through.

In the meantime, Bob has come back with a shorter version.

Pete