CountIf or SumProduct formula

[ultracyclist]

I want to count the number of cells that have the Value "Witchta 15" in column I and a date value in this format "mm/dd/yyyy" in column S.

Since the date will change with each row, I'm not sure which formula is correct, so it only counts a value of 1 for each row that matches the above criteria.

Thanks,

Allen

 

[Aladin Akyurek]

If there is a date criterion, what is it?

 

[ultracyclist]

I'm using the date format listed in my initial post. is that what your asking?

 

[Aladin Akyurek]

I'm using the date format listed in my initial post. is that what your asking?

What are "the above criteria"? One is: "Witchta 15". What is the other - a certain date or a certain month/year or what?

 

[ultracyclist]

ADVERTISEMENT

Here's a sample of the data

<style type="text/css">
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif;
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #ccc;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<tr class="tableizer-firstrow"><th>Wichita 15</th><th>3/27/2013</th></tr>
<tr><td>Wichita 15</td><td>3/28/2013</td></tr>
<tr><td>Wichita 15</td><td>3/29/2013</td></tr>
<tr><td>Wichita 15</td><td>3/30/2013</td></tr>
<tr><td>Dallas 2</td><td>3/31/2013</td></tr>
<tr><td>Dallas 2</td><td>4/1/2013</td></tr></table>


In this instance the formula would count a total of 4 since it has 4 dates that are associated with "Witchita 15"in the preceding column. In some cases the date may be the same for multiple rows. I'm not so concerned with that.

Hope this helps clarify things.

Thanks,

Allen

 

[ultracyclist]

Sorry it should count 3.

 

[Aladin Akyurek]

ADVERTISEMENT

Sorry it should count 3.

Try...

=SUMPRODUCT(--(A2:A6="Wichita 15"),--ISNUMBER(B2:B6))

 

[ultracyclist]

That worked.

Thank you

 

[Aladin Akyurek]

That worked.

Thank you

You are welcome. Thanks for providing feedback.