Excel CountIf formula

[bnsSteve]

Hello,

I'm trying to Count a range of cells (ex: Col C) that starts with "ABC" if it's in a certain month or date range (Col B)

I have a helper column for the dates, so if Col A is = January 5, then Col B = 1, Col A = February 13, then Col B = 2 etc.

Sample Data would look like this:
Col A----------Col B----Col C
Jan 1, 2015 ----1----ABC123
Jan 20, 2015----1----XYZ342
Feb 15, 2015----2----LMK543
Mar 18, 2015----3----QWE653

Is there a way to count the cells in Col C that start with ABC that belong to the month of January? Kinda of looking for something similar to the SumIF function but with a count and reading strings

Hope that's clear, thank you in advance for your help.
Steve


Running Crystal 11 - 14.0.2.364

 

[SkipVought]

Hi, this is not a Crystal forum. It's Microsoft Office.

Your history indicates that you often post in Business Objects: Crystal Reports 1 Formulas.

 

[bnsSteve]

Hi Skip,

Ya trying to do this in Excel 2010, is it still relevant in this forum?

Also, sorry forgot to mention that my data is in another Sheet.

Thanks,
Steve

Running Crystal 11 - 14.0.2.364

 

[SkipVought]

But is you were using Excel as opposed to "Running Crystal 11 - 14.0.2.364"

1. Use headings like
[tt]
dte num val
1-Jan-15 1 ABC123
20-Jan-15 1 XYZ342
15-Feb-15 2 LMK543
18-Mar-15 3 QWE653
[/tt]

2. Make named ranges using Formulas > Defined Names > Create names in TOP RPW

3. Use this formula
[tt]
=SUMPRODUCT((MONTH(dte)=1)*(LEFT(val,3)="ABC"))
[/tt]

 

[SkipVought]

sorry, I used the wrong tgml code

[pre]
dte mon val
1-Jan-15 1 ABC123
20-Jan-15 1 XYZ342
15-Feb-15 2 LMK543
18-Mar-15 3 QWE653
[/pre]


[tt]
=SUMPRODUCT((MONTH(dte)=1)*(LEFT(val,3)="ABC"))
[/tt]

 

[bnsSteve]

Hi Skip, thanks for the solution that worked great!

I was also playing around and found that the COUNTIFS function worked as well.

=COUNTIFS(Sheet1!B2:B5,1,Sheet1!C2:C5,"ABC*")

Sorry about the crystal confusion, I have it as my signature as I usually post in the crystal forum.

Thanks again for the quick reply!

Running Crystal 11 - 14.0.2.364

 

[SkipVought]

I got accustomed to using SUMPRODUCT() for counting & summing with multiple criteria, before Excel 2007.